Date and time functions

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

info

Checking if tables contain a designated timestamp column can be done via the tables() and table_columns() functions which are described in the meta functions documentation page.

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).

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

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).

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;

now#

now() - offset from UTC Epoch in microseconds.

Calculates UTC timestamp using system's real time clock. Unlike sysdatetime(), 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;

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:

  • T milliseconds
  • s seconds
  • m minutes
  • h hours
  • d days
  • M months
  • y year

timestamp - any timestamp value

Return value:

Return value type is timestamp.

Examples:

WITH t AS (SELECT cast('2016-02-10T16:18:22.862145Z' AS timestamp) ts)
SELECT
ts,
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_millic_secondc_minutec_hourc_dayc_monthc_year
2016-02-10T16:18:22.862145Z2016-02-10T16:18:22.863000Z2016-02-10T16:18:23.000000Z2016-02-10T16:19:00.000000Z2016-02-10T17:00:00.000000Z2016-02-11T00:00:00.000000Z2016-03-01T00:00:00.000000Z2017-01-01T00:00:00.000000Z"

timestamp_floor#

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

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

Arguments:

timestamp_floor(unit, timestamp) has the following arguments:

unit - may be one of the following:

  • T milliseconds
  • s seconds
  • m minutes
  • h hours
  • d days
  • M months
  • y year

timestamp - any timestamp value

Return value:

Return value type is timestamp.

Examples:

WITH t AS (SELECT cast('2016-02-10T16:18:22.862145Z' AS timestamp) ts)
SELECT
ts,
timestamp_floor('T', ts) f_milli,
timestamp_floor('s', ts) f_second,
timestamp_floor('m', ts) f_minute,
timestamp_floor('h', ts) f_hour,
timestamp_floor('d', ts) f_day,
timestamp_floor('M', ts) f_month,
timestamp_floor('y', ts) f_year
FROM t
tsf_millif_secondf_minutef_hourf_dayf_monthf_year
2016-02-10T16:18:22.862145Z2016-02-10T16:18:22.862000Z2016-02-10T16:18:22.000000Z2016-02-10T16:18:00.000000Z2016-02-10T16:00:00.000000Z2016-02-10T00:00:00.000000Z2016-02-01T00:00:00.000000Z2016-01-01T00:00:00.000000Z

to_timestamp#

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

Will convert a string to timestamp 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 date and 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

Examples:

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
matching with nanosecond precision
SELECT to_timestamp('2020-03-01:15:43:00.000000000Z', '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 partial or full form of yyyy-MM-ddTHH:mm:ss.SSSUUU or yyyy-MM-dd HH:mm:ss.SSSUUU with valid time offset +01:00 or Z. See more examples at Native timestamp format

to_date#

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 date and 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 format
SELECT to_date('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss')
FROM long_sequence(1);
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 date or timestamp value to a string in the specified format

Will convert a date or 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 date and timestamp format section.

Arguments:

  • value is any date or timestamp
  • 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_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 as Unix timestamp 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 type is timestamp

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 as Unix timestamp 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 type is timestamp

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

dateadd#

dateadd(period, n, startDate) - adds n period to startDate.

Arguments:

  • period is a char. Period to be added. Available periods are s, m, h, d, M, y.
  • n is an int. Number of periods to add.
  • startDate is a timestamp or date. Timestamp to add the periods to.

Return value:

Return value type is timestamp

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 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 s, m, h, d, M, y.
  • date1 and date2 are date or timestamp. Dates to compare

Return value:

Return value type is int

Examples:

Difference in days
SELECT datediff(
'd',
to_timestamp('2020-01-23','yyyy-MM-dd'),
to_timestamp('2020-01-27','yyyy-MM-dd'))
FROM long_sequence(1);
datediff
4
Difference in months
SELECT datediff(
'M',
to_timestamp('2020-01-23','yyyy-MM-dd'),
to_timestamp('2020-02-24','yyyy-MM-dd'))
FROM long_sequence(1);
datediff
1

millis#

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

Arguments:

  • value is any timestamp 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
Using in an aggregation
SELECT millis(ts), count() FROM transactions;
secondcount
02323
16548
......
9989876
9992567

micros#

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

Arguments:

  • value is any timestamp 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
Using in an aggregation
SELECT micros(ts), count() FROM transactions;
secondcount
02323
16548
......
9989876
9992567

second#

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

Arguments:

  • value is any timestamp 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

minute#

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

Arguments:

  • value is any timestamp or date

Return value:

Return value type is int

Examples:

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

hour#

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

Arguments:

  • value is any timestamp or date

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

day#

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

Arguments:

  • value is any timestamp or date

Return value:

Return value type is int

Examples:

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

month#

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

Arguments:

  • value is any timestamp 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

year#

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

Arguments:

  • value is any timestamp or date

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

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 or date

Return value:

Return value type is boolean

Examples:

SELECT year(ts), is_leap_year(ts) FROM myTable;
yearis_leap_year
2020true
2021false
2022false
2023false
2024true
2025false

days_in_month#

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

Arguments:

  • value is any timestamp 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

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 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 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

Date and Timestamp format#

The date and timestamp format is a combination of letters from the table below and arbitrary text. These letters are case-sensitive and are used as they are, without any prefix.

LetterDate or Time ComponentPresentationExamples
GEra designatorTextAD
yy single digit or greedy year, depending on the input digit numberYear1996; 96; 999; 3
yyTwo digit year of the current centuryYear96 (interpreted as 2096)
yyyThree-digit yearYear999
yyyyFour-digit yearYear1996
MMonth in yearMonthJuly; Jul; 07
wWeek in yearNumber27
WWeek in monthNumber2
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
SMillisecondNumber978
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
UMicrosecondNumber698
NNanosecondNumber125

Examples for greedy year format y#

The interpretation of y depends on the input digit number:

  • 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

See also#