Timestamp, date and time functions

This page describes the available functions to assist with performing time-based calculations using timestamps.

Timestamp format​

The timestamp format is formed by units and arbitrary text. A unit is a combination of letters representing a date or time component, as defined by the table below. The letters used to form a unit are case-sensitive.

See Timestamps in QuestDB for more examples of how the units are used to parse inputs.

UnitDate or Time ComponentPresentationExamples
GEra designatorTextAD
yy single digit or greedy year, depending on the number of digits in inputYear1996; 96; 999; 3
yyTwo digit year of the current centuryYear96 (interpreted as 2096)
yyyThree-digit yearYear999
yyyyFour-digit yearYear1996
MMonth in year, numeric, greedyMonth7; 07; 007; etc.
MMMonth in year, two-digitMonth07
MMMMonth in year, nameMonthJul; July
wWeek in yearNumber2
wwISO week of year (two-digit)Number02
DDay in yearNumber189
dDay in monthNumber10
FDay of week in monthNumber2
EDay name in weekTextTuesday; Tue
uDay number of week (1 = Monday, ..., 7 = Sunday)Number1
aAm/pm markerTextPM
HHour in day (0-23)Number0
kHour in day (1-24)Number24
KHour in am/pm (0-11)Number0
hHour in am/pm (1-12)Number12
mMinute in hourNumber30
sSecond in minuteNumber55
SSS3-digit millisecond (see explanation below for fraction-of-second)Number978
SMillisecond up to 3 digits (see explanation below for fraction-of-second)Number900
UUU3-digit microsecond (see explanation below for fraction-of-second)Number456
UMicrosecond up to 3 digits (see explanation below for fraction-of-second)Number456
U+Microsecond up to 6 digits (see explanation below for fraction-of-second)Number123456
NNanosecond up to 3 digits (see explanation below for fraction-of-second)Number900
N+Microsecond up to 9 digits (see explanation below for fraction-of-second)Number123456789
zTime zoneGeneral time zonePacific Standard Time; PST; GMT-08:00
ZTime zoneRFC 822 time zone-0800
xTime zoneISO 8601 time zone-08; -0800; -08:00

Examples for greedy year format y​

The interpretation of y depends on the number of digits in the input text:

  • If the input year is a two-digit number, the output timestamp assumes the current century.
  • Otherwise, the number is interpreted as it is.
Input yearTimestamp value interpreted by y-MNotes
5-030005-03-01T00:00:00.000000ZGreedily parsing the number as it is
05-032005-03-01T00:00:00.000000ZGreedily parsing the number assuming current century
005-030005-03-01T00:00:00.000000ZGreedily parsing the number as it is
0005-030005-03-01T00:00:00.000000ZGreedily parsing the number as it is

Examples for fractions of a second​

In a basic example, y-M-dTHH:mm:ss.S specifies to parse 1, 2, or 3 decimals. Here are more examples, showing just the last part starting with the .:

formatnumber of decimalsexample inputparsed fraction of second
.S1-3.1212 milliseconds
.SSS3.123123 milliseconds
.SSSU4-6.1234123,400 microseconds
.SSSUUU6.123456123,456 microseconds
.U+1-6.12345123,450 microseconds
.SSSUUUN7-9.1234567123,456,700 nanoseconds
.SSSUUUNNN9.123456789123,456,789 nanoseconds
.N+1-9.12120,000,000 nanoseconds

Timestamp to Date conversion​

As described at the data types section, the only difference between TIMESTAMP, TIMESTAMP_NS, and DATE in QuestDB type system is the resolution. Whilst TIMESTAMP stores resolution as an offset from Unix epoch in microseconds, TIMESTAMP_NS stores it as an offset in nanoseconds, and DATE stores the offset in milliseconds.

Since the three types are backed by a signed long, this means the DATE type has a wider range. A DATE column can store about ±2.9 million years from the Unix epoch, whereas a TIMESTAMP has an approximate range of ±290,000 years, and a TIMESTAMP_NS has an approximate range of ±2262 years.

For most purposes a TIMESTAMP is preferred, as it offers a wider range of functions whilst still being 8 bytes in size.

Be aware that, when using a TIMESTAMP or TIMESTAMP_NS as the designated timestamp, you cannot set it to any value before the Unix epoch (1970-01-01T00:00:00.000000Z).

To explicitly convert from TIMESTAMP to DATE or TIMESTAMP_NS, you can use CAST(ts_column AS DATE) or CAST(ts_column AS TIMESTAMP_NS). To convert from DATE or TIMESTAMP_NS to TIMESTAMP you can CAST(column AS TIMESTAMP_NS).

Programmatically convert from language-specific datetimes into QuestDB timestamps​

Different programming languages use different types of objects to represent the DATE type. To learn how to convert from the DATE type into a TIMESTAMP object in Python, Go, Java, JavaScript, C/C++, Rust, or C#/.NET, please visit our Date to Timestamp conversion reference.

Function Reference

date_trunc​

date_trunc(unit, timestamp) - returns a timestamp truncated to the specified precision.

Arguments:

  • unit is one of the following:

    • millennium
    • decade
    • century
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • millisecond
    • microsecond
    • nanosecond

  • timestamp is any timestamp, timestamp_ns, or ISO-8601 string value.

Return value:

Return value defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

SELECT date_trunc('hour', '2022-03-11T22:00:30.555555Z') hour,
date_trunc('month', '2022-03-11T22:00:30.555555Z') month,
date_trunc('year','2022-03-11T22:00:30.555555Z') year;
date_trunc('year','2022-03-11T22:00:30.555555000Z') year;
hour (timestamp_ns)month (timestamp_ns)year (timestamp_ns)year (timestamp_ns)
2022-03-11T22:00:00.000000Z2022-03-01T00:00:00.000000Z2022-01-01T00:00:00.000000Z2022-01-01T00:00:00.000000000Z

dateadd​

dateadd(period, n, startDate[, timezone]) - adds n period to startDate, optionally respecting timezone DST transitions.

tip

When a timezone is specified, the function handles daylight savings time transitions correctly. This is particularly important when adding periods that could cross DST boundaries (like weeks, months, or years).

Without the timezone parameter, the function performs simple UTC arithmetic which may lead to incorrect results when crossing DST boundaries. For timezone-aware calculations, use the timezone parameter.

Arguments:

  • period is a char. Period to be added. Available periods are:

    • n: nanoseconds
    • u: microseconds
    • T: milliseconds
    • s: second
    • m: minute
    • h: hour
    • d: day
    • w: week
    • M: month
    • y: year

  • n is an int indicating the number of periods to add.

  • startDate is a timestamp, timestamp_ns, or date indicating the timestamp to add the period to.

  • timezone (optional) is a string specifying the timezone to use for DST-aware calculations - for example, 'Europe/London'.

Return value:

Return value type defaults to timestamp, but it will return a timestamp_ns if the startDate argument is a timetamp_ns.

Examples:

Adding hours
SELECT systimestamp(), dateadd('h', 2, systimestamp())
FROM long_sequence(1);
systimestampdateadd
2020-04-17T00:30:51.380499Z2020-04-17T02:30:51.380499Z
Adding days
SELECT systimestamp(), dateadd('d', 2, systimestamp())
FROM long_sequence(1);
systimestampdateadd
2020-04-17T00:30:51.380499Z2020-04-19T00:30:51.380499Z
Adding weeks with timezone
SELECT
    '2024-10-21T10:00:00Z',
    dateadd('w', 1, '2024-10-21T10:00:00Z', 'Europe/Bratislava') as with_tz,
    dateadd('w', 1, '2024-10-21T10:00:00Z') as without_tz
FROM long_sequence(1);
timestampwith_tzwithout_tz
2024-10-21T10:00:00.000Z2024-10-28T10:00:00.000Z2024-10-28T09:00:00.000Z

Note how the timezone-aware calculation correctly handles the DST transition in Europe/Bratislava.

Adding months
SELECT systimestamp(), dateadd('M', 2, systimestamp())
FROM long_sequence(1);
systimestampdateadd
2020-04-17T00:30:51.380499Z2020-06-17T00:30:51.380499Z

datediff​

datediff(period, date1, date2) - returns the absolute number of period between date1 and date2.

Arguments:

  • period is a char. Period to be added. Available periods are:

    • n: nanoseconds
    • u: microseconds
    • T: milliseconds
    • s: second
    • m: minute
    • h: hour
    • d: day
    • w: week
    • M: month
    • y: year

  • date1 and date2 are timestamp, timestamp_ns, date, or date literal strings defining the dates to compare.

Return value:

Return value type is long

Examples:

Difference in days
SELECT datediff('d', '2020-01-23', '2020-01-27');
datediff
4
Difference in months
SELECT datediff('M', '2020-01-23', '2020-02-27');
datediff
1

day​

day(value) - returns the day of month for a given timestamp from 1 to 31.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Day of the monthDemo this query
SELECT day(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM trades
LIMIT -1;
day
01
Using in an aggregation
SELECT day(ts), count() FROM transactions;
daycount
12323
26548
......
309876
312567

day_of_week​

day_of_week(value) - returns the day number in a week from 1 (Monday) to 7 (Sunday).

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

SELECT to_str(ts,'EE'),day_of_week(ts) FROM myTable;
dayday_of_week
Monday1
Tuesday2
Wednesday3
Thursday4
Friday5
Saturday6
Sunday7

day_of_week_sunday_first​

day_of_week_sunday_first(value) - returns the day number in a week from 1 (Sunday) to 7 (Saturday).

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

SELECT to_str(ts,'EE'),day_of_week_sunday_first(ts) FROM myTable;
dayday_of_week_sunday_first
Monday2
Tuesday3
Wednesday4
Thursday5
Friday6
Saturday7
Sunday1

days_in_month​

days_in_month(value) - returns the number of days in a month from a given timestamp or date.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

SELECT month(ts), days_in_month(ts) FROM myTable;
monthdays_in_month
430
531
630
731
831

extract​

extract(unit, timestamp) - returns the selected time unit from the input timestamp.

Arguments:

  • unit is one of the following:

    • millennium
    • epoch
    • decade
    • century
    • year
    • isoyear
    • doy (day of year)
    • quarter
    • month
    • week
    • dow (day of week)
    • isodow
    • day
    • hour
    • minute
    • second
    • microseconds
    • milliseconds

  • timestamp is any timestamp, timestamp_ns, date, or date literal string value.

Return value:

Return value type is integer.

Examples


SELECT extract(millennium from '2023-03-11T22:00:30.555555Z') millennium,
extract(year from '2023-03-11T22:00:30.555555Z') year,
extract(month from '2023-03-11T22:00:30.555555Z') month,
extract(week from '2023-03-11T22:00:30.555555Z') quarter,
extract(hour from '2023-03-11T22:00:30.555555Z') hour,
extract(second from '2023-03-11T22:00:30.555555Z') second;
millenniumyearmonthquarterhoursecond
320233102230

hour​

hour(value) - returns the hour of day for a given timestamp from 0 to 23.

Arguments:

  • timestamp is any timestamp, timestamp_ns, date, or date literal string value.

Return value:

Return value type is int

Examples:

Hour of the day
SELECT hour(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);
hour
12
Using in an aggregation
SELECT hour(ts), count() FROM transactions;
hourcount
02323
16548
......
229876
232567

interval​

interval(start_timestamp, end_timestamp) - creates a time interval from two timestamps.

Arguments:

  • start_timestamp is a timestamp.
  • end_timestamp is a timestamp not earlier than the start_timestamp.

Return value:

Return value type is interval.

Examples:

Construct an intervalDemo this query
SELECT interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
interval
('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')

interval_start​

interval_start(interval) - extracts the lower bound of the interval.

Arguments:

  • interval is an interval.

Return value:

Return value type is timestamp or timestamp_ns, depending on the type of values in the interval.

Examples:

Extract an interval lower boundDemo this query
SELECT
  interval_start(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )
interval_start
2024-10-08T11:09:47.573000Z

interval_end​

interval_end(interval) - extracts the upper bound of the interval.

Arguments:

  • interval is an interval.

Return value:

Return value type is timestamp or timestamp_ns, depending on the type of values in the interval.

Examples:

Extract an interval upper boundDemo this query
SELECT
  interval_end(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )
interval_end
2024-10-09T11:09:47.573000Z

is_leap_year​

is_leap_year(value) - returns true if the year of value is a leap year, false otherwise.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is boolean

Examples:

Simple exampleDemo this query
SELECT year(timestamp), is_leap_year(timestamp)
FROM trades
limit -1;
yearis_leap_year
2020true
2021false
2022false
2023false
2024true
2025false

micros​

micros(value) - returns the micros of the millisecond for a given date or timestamp from 0 to 999.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Micros of the second
SELECT micros(to_timestamp('2020-03-01:15:43:21.123456', 'yyyy-MM-dd:HH:mm:ss.SSSUUU'))
FROM long_sequence(1);
millis
456
Parsing 3 digits when no unit is added after U
SELECT micros(to_timestamp('2020-03-01:15:43:21.123456', 'yyyy-MM-dd:HH:mm:ss.SSSU'))
FROM long_sequence(1);
millis
456
Using in an aggregation
SELECT micros(ts), count() FROM transactions;
secondcount
02323
16548
......
9989876
9992567

millis​

millis(value) - returns the millis of the second for a given date or timestamp from 0 to 999.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Millis of the second
SELECT millis(
    to_timestamp('2020-03-01:15:43:21.123456', 'yyyy-MM-dd:HH:mm:ss.SSSUUU'))
FROM long_sequence(1);
millis
123
Parsing 3 digits when no unit is added after S
SELECT millis(to_timestamp('2020-03-01:15:43:21.123', 'yyyy-MM-dd:HH:mm:ss.S'))
FROM long_sequence(1);
millis
123
Using in an aggregation
SELECT millis(ts), count() FROM transactions;
secondcount
02323
16548
......
9989876
9992567

minute​

minute(value) - returns the minute of the hour for a given timestamp from 0 to 59.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Minute of the hourDemo this query
SELECT minute(to_timestamp('2022-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM trades
LIMIT -1;
minute
43
Using in an aggregation
SELECT minute(ts), count() FROM transactions;
minutecount
02323
16548
......
589876
592567

month​

month(value) - returns the month of year for a given date from 1 to 12.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Month of the year
SELECT month(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);
month
03
Using in an aggregation
SELECT month(ts), count() FROM transactions;
monthcount
12323
26548
......
119876
122567

nanos​

nanos(value) - returns the nanos of the second for a given date or timestamp from 0 to 999.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Nanos of the second
SELECT nanos(
    to_timestamp_ns('2020-03-01:15:43:21.123456789', 'yyyy-MM-dd:HH:mm:ss.SSSUUUNNN')) as nanos
FROM long_sequence(1);
nanos
789

now​

now() - offset from UTC Epoch in microseconds.

Calculates UTC timestamp using system's real time clock. Unlike systimestamp(), it does not change within the query execution timeframe and should be used in WHERE clause to filter designated timestamp column relative to current time, i.e.:

  • SELECT now() FROM long_sequence(200) will return the same timestamp for all rows
  • SELECT systimestamp() FROM long_sequence(200) will have new timestamp values for each row

Arguments:

  • now() does not accept arguments.

Return value:

Return value type is timestamp.

Examples:

Filter records to created within last day
SELECT created, origin FROM telemetry
WHERE created > dateadd('d', -1, now());
createdorigin
2021-02-01T21:51:34.443726Z1
Query returns same timestamp in every row
SELECT now() FROM long_sequence(3)
now
2021-02-01T21:51:34.443726Z
2021-02-01T21:51:34.443726Z
2021-02-01T21:51:34.443726Z
Query based on last minute
SELECT * FROM readings
WHERE date_time > now() - 60000000L;

pg_postmaster_start_time​

pg_postmaster_start_time() - returns the time when the server started.

Arguments

  • pg_postmaster_start_time() does not accept arguments.

Return value:

Return value type is timestamp

Examples

SELECT pg_postmaster_start_time();
pg_postmaster_start_time
2023-03-30T16:20:29.763961Z

second​

second(value) - returns the second of the minute for a given date or timestamp from 0 to 59.

Arguments:

  • value is any timestamp, timestamp_ns, or date

Return value:

Return value type is int

Examples:

Second of the minute
SELECT second(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);
second
43
Using in an aggregation
SELECT second(ts), count() FROM transactions;
secondcount
02323
16548
......
589876
592567

today, tomorrow, yesterday​

  • today() - returns an interval representing the current day.

  • tomorrow() - returns an interval representing the next day.

  • yesterday() - returns an interval representing the previous day.

Interval is in the UTC/GMT+0 timezone.

Arguments:

No arguments taken.

Return value:

Return value is of type interval.

Examples:

Using today
SELECT true as in_today FROM long_sequence(1)
WHERE now() IN today();

today, tomorrow, yesterday with timezone​

  • today(timezone) - returns an interval representing the current day with timezone adjustment.

  • tomorrow(timezone) - returns an interval representing the next day timezone adjustment.

  • yesterday(timezone) - returns an interval representing the previous day timezone adjustment.

Arguments:

timezone is a string matching a timezone.

Return value:

Return value is of type interval.

Examples:

Using todayDemo this query
SELECT today() as today, today('CEST') as adjusted;
todayadjusted
('2024-10-08T00:00:00.000Z', '2024-10-08T23:59:59.999Z')('2024-10-07T22:00:00.000Z', '2024-10-08T21:59:59.999Z')

This function allows the user to specify their local timezone and receive a UTC interval that corresponds to their 'day'.

In this example, CEST is a +2h offset, so the CEST day started at 10:00 PM UTC the day before.

sysdate​

sysdate() - returns the timestamp of the host system as a date with millisecond precision.

Calculates UTC date with millisecond precision using system's real time clock. The value is affected by discontinuous jumps in the system time (e.g., if the system administrator manually changes the system time).

sysdate() value can change within the query execution timeframe and should NOT be used in WHERE clause to filter designated timestamp column.

tip

Use now() with WHERE clause filter.

Arguments:

  • sysdate() does not accept arguments.

Return value:

Return value type is date.

Examples:

Insert current system date along with a value
INSERT INTO readings
VALUES(sysdate(), 123.5);
sysdatereading
2020-01-02T19:28:48.727516Z123.5
Query based on last minute
SELECT * FROM readings
WHERE date_time > sysdate() - 60000000L;

systimestamp​

systimestamp() - offset from UTC Epoch in microseconds. Calculates UTC timestamp using system's real time clock. The value is affected by discontinuous jumps in the system time (e.g., if the system administrator manually changes the system time).

systimestamp() value can change within the query execution timeframe and should NOT be used in WHERE clause to filter designated timestamp column.

tip

Use now() with WHERE clause filter.

Arguments:

  • systimestamp() does not accept arguments.

Return value:

Return value type is timestamp.

Examples:

Insert current system timestamp
INSERT INTO readings
VALUES(systimestamp(), 123.5);
tsreading
2020-01-02T19:28:48.727516Z123.5

systimestamp_ns​

systimestamp_ns() - offset from UTC Epoch in nanoseconds. Calculates UTC timestamp using system's real time clock. The value is affected by discontinuous jumps in the system time (e.g., if the system administrator manually changes the system time).

systimestamp_ns() value can change within the query execution timeframe and should NOT be used in WHERE clause to filter designated timestamp column.

tip

Use now() with WHERE clause filter.

Arguments:

  • systimestamp_ns() does not accept arguments.

Return value:

Return value type is timestamp_ns.

Examples:

Insert current system timestamp_ns
INSERT INTO readings
VALUES(systimestamp_ns(), 123.5);
tsreading
2020-01-02T19:28:48.727516132Z123.5

timestamp_ceil​

timestamp_ceil(unit, timestamp) - performs a ceiling calculation on a timestamp by given unit.

A unit must be provided to specify which granularity to perform rounding.

Arguments:

timestamp_ceil(unit, timestamp) has the following arguments:

unit - may be one of the following:

  • n nanoseconds
  • U microseconds
  • T milliseconds
  • s seconds
  • m minutes
  • h hours
  • d days
  • M months
  • y year

timestamp - any timestamp, timestamp_ns, date, or date literal string value.

Return value:

Return value type defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

WITH t AS (SELECT cast('2016-02-10T16:18:22.862145333Z' AS timestamp_ns) ts)
SELECT
  ts,
  timestamp_ceil('n', ts) c_nano,
  timestamp_ceil('U', ts) c_micro,
  timestamp_ceil('T', ts) c_milli,
  timestamp_ceil('s', ts) c_second,
  timestamp_ceil('m', ts) c_minute,
  timestamp_ceil('h', ts) c_hour,
  timestamp_ceil('d', ts) c_day,
  timestamp_ceil('M', ts) c_month,
  timestamp_ceil('y', ts) c_year
  FROM t
tsc_nanoc_microc_millic_secondc_minutec_hourc_dayc_monthc_year
2016-02-10T16:18:22.862145333Z2016-02-10T16:18:22.862145333Z2016-02-10T16:18:22.862146000Z2016-02-10T16:18:22.863000000Z2016-02-10T16:18:23.000000000Z

timestamp_floor​

timestamp_floor(interval, timestamp) - performs a floor calculation on a timestamp by given interval expression.

An interval expression must be provided to specify which granularity to perform rounding for.

Arguments:

timestamp_floor(interval, timestamp) has the following arguments:

unit - is a time interval expression that may use one of the following suffices:

  • n nanoseconds
  • U microseconds
  • T milliseconds
  • s seconds
  • m minutes
  • h hours
  • d days
  • M months
  • y year

timestamp - any timestamp, timestamp_ns, date, or date literal string value.

Return value:

Return value type defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

SELECT timestamp_floor('5d', '2018-01-01')

Gives:

timestamp_floor
2017-12-30T00:00:00.000000Z

The number part of the expression is optional:

WITH t AS (SELECT cast('2016-02-10T16:18:22.862145333Z' AS timestamp_ns) ts)
SELECT
  ts,
  timestamp_floor('n', ts) c_nano,
  timestamp_floor('U', ts) c_micro,
  timestamp_floor('T', ts) c_milli,
  timestamp_floor('s', ts) c_second,
  timestamp_floor('m', ts) c_minute,
  timestamp_floor('h', ts) c_hour,
  timestamp_floor('d', ts) c_day,
  timestamp_floor('M', ts) c_month,
  timestamp_floor('y', ts) c_year
  FROM t

Gives:

tsc_nanoc_microc_millic_secondc_minutec_hourc_dayc_monthc_year
2016-02-10T16:18:22.862145333Z2016-02-10T16:18:22.862145333Z2016-02-10T16:18:22.862145000Z2016-02-10T16:18:22.862000000Z2016-02-10T16:18:22.000000000Z

timestamp_floor with offset​

When timestamps are floored by timestamp_floor(interval, timestamp), they are based on a root timestamp of 0. This means that some floorings with a stride can be confusing, since they are based on a modulo from 1970-01-01.

For example:

SELECT timestamp_floor('5d', '2018-01-01')

Gives:

timestamp_floor
2017-12-30T00:00:00.000000Z

If you wish to calculate bins from an offset other than 1970-01-01, you can add a third parameter: timestamp_floor(interval, timestamp, offset). The offset acts as a baseline from which further values are calculated.

SELECT timestamp_floor('5d', '2018-01-01', '2018-01-01')

Gives:

timestamp_floor
2018-01-01T00:00:00.000000Z

You can test this on the QuestDB Demo:

SELECT timestamp_floor('5d', pickup_datetime, '2018') t, count
FROM trips
WHERE pickup_datetime in '2018'
ORDER BY 1;

Gives:

tcount
2018-01-01T00:00:00.000000Z1226531
2018-01-06T00:00:00.000000Z1468302
2018-01-11T00:00:00.000000Z1604016
2018-01-16T00:00:00.000000Z1677303
......

timestamp_shuffle​

timestamp_shuffle(timestamp_1, timestamp_2) - generates a random timestamp inclusively between the two input timestamps.

Arguments:

  • timestamp_1 - any timestamp, timestamp_ns, date, or date literal string value.
  • timestamp_2 - a timestamp value that is not equal to timestamp_1

Return value:

Return value type defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

SELECT timestamp_shuffle('2023-03-31T22:00:30.555998Z', '2023-04-01T22:00:30.555998Z');
timestamp_shuffle
2023-04-01T11:44:41.893394Z

to_date​

note

While the date data type is available, we highly recommend applying the timestamp data type in its place.

The only material advantage of date is a wider time range; timestamp however is adequate in virtually all cases.

Date supports fewer functions and uses milliseconds instead of microseconds.

to_date(string, format) - converts string to date by using the supplied format to extract the value.

Will convert a string to date using the format definition passed as an argument. When the format definition does not match the string input, the result will be null.

For more information about recognized timestamp formats, see the timestamp format section.

Arguments:

  • string is any string that represents a date and/or time.
  • format is a string that describes the date format in which string is expressed.

Return value:

Return value type is date

Examples:

string matches formatDemo this query
SELECT to_date('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss')
FROM trades;
to_date
2020-03-01T15:43:21.000Z
string does not match format
SELECT to_date('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);
to_date
null
Using with INSERT
INSERT INTO measurements
values(to_date('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.5);
datevalue
2019-12-12T12:15:00.000Z123.5

to_str​

to_str(value, format) - converts timestamp value to a string in the specified format.

Will convert a timestamp value to a string using the format definition passed as an argument. When elements in the format definition are unrecognized, they will be passed-through as string.

For more information about recognized timestamp formats, see the timestamp format section.

Arguments:

  • value is any date, timestamp, or timestamp_ns value
  • format is a timestamp format.

Return value:

Return value type is string

Examples:

  • Basic example
SELECT to_str(systimestamp(), 'yyyy-MM-dd') FROM long_sequence(1);
to_str
2020-03-04
  • With unrecognized timestamp definition
SELECT to_str(systimestamp(), 'yyyy-MM-dd gooD DAY 123') FROM long_sequence(1);
to_str
2020-03-04 gooD DAY 123

to_timestamp​

to_timestamp(string, format) - converts string to timestamp by using the supplied format to extract the value with microsecond precision.

When the format definition does not match the string input, the result will be null.

For more information about recognized timestamp formats, see the timestamp format section.

Arguments:

  • string is any string that represents a date and/or time.
  • format is a string that describes the timestamp format in which string is expressed.

Return value:

Return value type is timestamp. QuestDB provides timestamp with microsecond resolution. Input strings with nanosecond precision will be parsed but lose the precision. Use to_timestamp_ns if nanosecond precision is required.

Examples:

Pattern matching with microsecond precision
SELECT to_timestamp('2020-03-01:15:43:21.127329', 'yyyy-MM-dd:HH:mm:ss.SSSUUU')
FROM long_sequence(1);
to_timestamp
2020-03-01T15:43:21.127329Z
Precision loss when pattern matching with nanosecond precision
SELECT to_timestamp('2020-03-01:15:43:00.000000001Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUNNNZ')
FROM long_sequence(1);
to_timestamp
2020-03-01T15:43:00.000000Z
String does not match format
SELECT to_timestamp('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);
to_timestamp
null
Using with INSERT
INSERT INTO measurements
values(to_timestamp('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.5);
timestampvalue
2019-12-12T12:15:00.000000Z123.5

Note that conversion of ISO timestamp format is optional. QuestDB automatically converts string to timestamp if it is a partial or full form of yyyy-MM-ddTHH:mm:ss.SSSUUU or yyyy-MM-dd HH:mm:ss.SSSUUU with a valid time offset, +01:00 or Z. See more examples in Native timestamp

to_timestamp_ns​

to_timestamp_ns(string, format) - converts string to timestamp_ns by using the supplied format to extract the value with nanosecond precision.

When the format definition does not match the string input, the result will be null.

For more information about recognized timestamp formats, see the timestamp format section.

Arguments:

  • string is any string that represents a date and/or time.
  • format is a string that describes the timestamp format in which string is expressed.

Return value:

Return value type is timestamp_ns. If nanoseconds are not needed, you can use to_timestamp instead.

Examples:

Pattern matching with nanosecond precision
SELECT to_timestamp_ns('2020-03-01:15:43:21.127329512', 'yyyy-MM-dd:HH:mm:ss.SSSUUUNNN') as timestamp_ns
FROM long_sequence(1);
timestamp_ns
2020-03-01T15:43:21.127329512Z

to_timezone​

to_timezone(timestamp, timezone) - converts a timestamp value to a specified timezone. For more information on the time zone database used for this function, see the QuestDB time zone database documentation.

Arguments:

  • timestamp is any timestamp, timestamp_ns, microsecond Epoch, or string equivalent
  • timezone may be Country/City tz database name, time zone abbreviation such as PST or in UTC offset in string format.

Return value:

Return value defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

  • Unix UTC timestamp in microseconds to Europe/Berlin
SELECT to_timezone(1623167145000000, 'Europe/Berlin')
to_timezone
2021-06-08T17:45:45.000000Z
  • Unix UTC timestamp in microseconds to PST by UTC offset
SELECT to_timezone(1623167145000000, '-08:00')
to_timezone
2021-06-08T07:45:45.000000Z
  • Timestamp as string to PST
SELECT to_timezone('2021-06-08T13:45:45.000000Z', 'PST')
to_timezone
2021-06-08T06:45:45.000000Z

to_utc​

to_utc(timestamp, timezone) - converts a timestamp by specified timezone to UTC. May be provided a timezone in string format or a UTC offset in hours and minutes. For more information on the time zone database used for this function, see the QuestDB time zone database documentation.

Arguments:

  • timestamp is any timestamp, timestamp_ns, microsecond Epoch, or string equivalent
  • timezone may be Country/City tz database name, time zone abbreviation such as PST or in UTC offset in string format.

Return value:

Return value defaults to timestamp, but it will return a timestamp_ns if the timestamp argument is of type timestamp_ns or if the date passed as a string contains nanoseconds resolution.

Examples:

  • Convert a Unix timestamp in microseconds from the Europe/Berlin timezone to UTC
SELECT to_utc(1623167145000000, 'Europe/Berlin')
to_utc
2021-06-08T13:45:45.000000Z
  • Unix timestamp in microseconds from PST to UTC by UTC offset
SELECT to_utc(1623167145000000, '-08:00')
to_utc
2021-06-08T23:45:45.000000Z
  • Timestamp as string in PST to UTC
SELECT to_utc('2021-06-08T13:45:45.000000Z', 'PST')
to_utc
2021-06-08T20:45:45.000000Z

week_of_year​

week_of_year(value) - returns the number representing the week number in the year.

Arguments:

  • value is any timestamp, timestamp_ns, date, or date string literal.

Return value:

Return value type is int

Examples

SELECT week_of_year('2023-03-31T22:00:30.555998Z');
week_of_year
13

year​

year(value) - returns the year for a given timestamp

Arguments:

  • value is any timestamp, timestamp_ns, date, or date string literal.

Return value:

Return value type is int

Examples:

Year
SELECT year(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);
year
2020
Using in an aggregation
SELECT month(ts), count() FROM transactions;
yearcount
20152323
20169876
20172567