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 INTO readings
VALUES(systimestamp(), 123.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 INTO readings
VALUES(sysdate(), 123.5);

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:

SELECT created, origin FROM telemetry
WHERE created > dateadd('d', -1, now());

SELECT now() FROM long_sequence(3)

SELECT * FROM readings
WHERE date_time > now() - 60000000L;

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:

SELECT to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss')
FROM long_sequence(1);

SELECT to_timestamp('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);

INSERT INTO measurements
values(to_timestamp('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.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:

SELECT to_date('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss')
FROM long_sequence(1);

SELECT to_date('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);

INSERT INTO measurements
values(to_date('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.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);

• With unrecognized timestamp definition

SELECT to_str(systimestamp(), 'yyyy-MM-dd gooD DAY 123') FROM long_sequence(1);

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:

SELECT systimestamp(), dateadd('h', 2, systimestamp())
FROM long_sequence(1);

SELECT systimestamp(), dateadd('d', 2, systimestamp())
FROM long_sequence(1);

SELECT systimestamp(), dateadd('M', 2, systimestamp())
FROM long_sequence(1);

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:

select datediff(
    'd',
    to_timestamp('2020-01-23','yyyy-MM-dd'),
    to_timestamp('2020-01-27','yyyy-MM-dd'))
from long_sequence(1);

select datediff(
    'M',
    to_timestamp('2020-01-23','yyyy-MM-dd'),
    to_timestamp('2020-02-24','yyyy-MM-dd'))
from long_sequence(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:

SELECT millis(
    to_timestamp('2020-03-01:15:43:21.123456', 'yyyy-MM-dd:HH:mm:ss.SSSUUU'))
FROM long_sequence(1);

select millis(ts), count() from transactions;

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:

SELECT micros(to_timestamp('2020-03-01:15:43:21.123456', 'yyyy-MM-dd:HH:mm:ss.SSSUUU'))
FROM long_sequence(1);

select micros(ts), count() from transactions;

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:

SELECT second(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select second(ts), count() from transactions;

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:

SELECT minute(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select minute(ts), count() from transactions;

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:

SELECT hour(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select hour(ts), count() from transactions;

day

day(value) - returns the day of month 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:

SELECT day(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select day(ts), count() from transactions;

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:

SELECT month(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select month(ts), count() from transactions;

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:

SELECT year(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);

select month(ts), count() from transactions;

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;

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;

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;

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;

Date and Timestamp format

Format is a combination of letters from table below combined with arbitrary text. Format letters are case-sensitive and are used as is (e.g. without any prefix)

See also

• to_timestamp
• to_date
• to_str