Timestamp, date and time functions

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

Timestamp and date types

QuestDB has three temporal types with different precision:

All three are stored as signed 64-bit integers representing offsets from the Unix epoch. TIMESTAMP is recommended for most use cases as it offers the best balance of precision and function support.

For details on all data types, see the data types overview.

:::note Designated timestamp restriction When used as a designated timestamp, TIMESTAMP and TIMESTAMP_NS values cannot be before the Unix epoch (1970-01-01T00:00:00.000000Z). :::

Converting between types

Use CAST to convert between temporal types:

-- Reduce precision
SELECT CAST(ts_column AS DATE) FROM my_table;

-- Increase precision
SELECT CAST(date_column AS TIMESTAMP) FROM my_table;
SELECT CAST(ts_column AS TIMESTAMP_NS) FROM my_table;

Converting from programming languages

To convert language-specific datetime objects (Python datetime, Java Instant, etc.) into QuestDB timestamps, see the Date to Timestamp conversion reference for Python, Go, Java, JavaScript, C/C++, Rust, and C#/.NET.

Function categories

Current time

Extraction

Arithmetic

Conversion

Interval construction

Utilities

:::tip Filtering vs projection

For filtering (WHERE clause): Use TICK syntax for optimized interval scans:

SELECT * FROM trades WHERE ts IN '$today'
SELECT * FROM trades WHERE ts IN '$now - 1h..$now'

For projection (SELECT clause): Use these functions for computed values:

SELECT dateadd('h', 2, ts) as shifted_time FROM trades
SELECT year(ts), month(ts) FROM trades

TICK syntax leverages interval scans for efficient filtering. Functions are for transformations and calculations. :::

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.555555555Z') year2;

dateadd

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

Use in projections (SELECT clause) to shift timestamps. For filtering relative time windows in WHERE clauses, prefer TICK syntax (e.g., $now - 1h..$now) for optimized interval scans.

:::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 timestamp_ns.

Examples:

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

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

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

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

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

See also

• datediff - Difference between timestamps
• TICK syntax - For filtering with optimized interval scans

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:

SELECT datediff('d', '2020-01-23', '2020-01-27');

SELECT datediff('M', '2020-01-23', '2020-02-27');

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:

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

SELECT day(ts), count() FROM transactions;

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;

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;

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;

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

• 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') week,
extract(hour from '2023-03-11T22:00:30.555555Z') hour,
extract(second from '2023-03-11T22:00:30.555555Z') second;

See also

• year, month, day, hour, minute, second - Individual extraction functions

hour

hour(timestamp) - 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:

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;

interval

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

Intervals are runtime-only values that cannot be stored in tables. Use this function for:

• Checking if a timestamp falls within a range: ts IN interval(start, end)
• Extracting bounds with interval_start() and interval_end()
• Working with intervals returned by today(), tomorrow(), yesterday()

For filtering in WHERE clauses, prefer TICK syntax (e.g., $today, $now - 1h..$now) which enables interval scan optimization.

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:

SELECT interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')

See also

• interval_start - Extract interval lower bound
• interval_end - Extract interval upper bound
• TICK syntax - For filtering with optimized interval scans

interval_start

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

Use to extract bounds from intervals returned by functions or stored in columns.

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:

SELECT
  interval_start(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )

interval_end

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

Use to extract bounds from intervals returned by functions or stored in columns.

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:

SELECT
  interval_end(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )

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:

SELECT year(timestamp), is_leap_year(timestamp)
FROM trades
limit -1;

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:

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

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

SELECT micros(ts), count() FROM transactions;

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:

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

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

SELECT millis(ts), count() FROM transactions;

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:

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

SELECT minute(ts), count() FROM transactions;

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:

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;

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:

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

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:

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

SELECT now() FROM long_sequence(3)

SELECT * FROM trades
WHERE timestamp > now() - 60000000L;

now_ns

now_ns() - offset from UTC Epoch in nanoseconds.

Calculates UTC timestamp using system's real time clock with nanosecond precision. Like now(), it does not change within the query execution timeframe.

Arguments:

• now_ns() does not accept arguments.

Return value:

Return value type is timestamp_ns.

Examples:

SELECT now_ns() FROM long_sequence(3)

See also

• now - Current timestamp with microsecond precision
• systimestamp_ns - Current timestamp with nanosecond precision (changes per row)

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();

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:

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;

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.

These functions return intervals for use in projections or comparisons. For filtering in WHERE clauses, prefer TICK syntax ($today, $tomorrow, $yesterday) which enables interval scan optimization.

Arguments:

No arguments taken.

Return value:

Return value is of type interval.

Examples:

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:

SELECT today() as today, today('CEST') as adjusted;

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.

See also

• TICK syntax - Use $today, $tomorrow, $yesterday for optimized filtering

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

SELECT * FROM trades
WHERE timestamp > 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 INTO readings
VALUES(systimestamp(), 123.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 INTO readings
VALUES(systimestamp_ns(), 123.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
• w weeks
• 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

timestamp_floor

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

Use for custom time bucketing in projections. For time-series aggregation, consider SAMPLE BY which provides optimized grouping with fill options.

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
• w weeks
• 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:

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:

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:

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:

You can test this on the QuestDB Demo:

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

Gives:

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');

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:

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

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

• With unrecognized timestamp definition

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

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:

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

SELECT to_timestamp('2020-03-01:15:43:00.000000001Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUNNNZ')
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 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

See also

• to_timestamp_ns - Parse string to timestamp with nanosecond precision
• to_str - Format timestamp as string
• Timestamp format - Format pattern reference

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:

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

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

• Unix UTC timestamp in microseconds to PST by UTC offset

SELECT to_timezone(1623167145000000, '-08:00')

• Timestamp as string to PST

SELECT to_timezone('2021-06-08T13:45:45.000000Z', 'PST')

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

• Unix timestamp in microseconds from PST to UTC by UTC offset

SELECT to_utc(1623167145000000, '-08:00')

• Timestamp as string in PST to UTC

SELECT to_utc('2021-06-08T13:45:45.000000Z', 'PST')

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');

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:

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

SELECT year(ts), count() FROM transactions;

See also

• extract - Extract any time unit from timestamp

Appendix: Timestamp format patterns {#timestamp-format}

Format patterns tell QuestDB how to interpret string timestamps. They are used in multiple contexts:

• SQL functions: to_timestamp() and to_timestamp_ns() for parsing text into native timestamp values
• CSV import: The timestamp parameter in COPY and the REST API
• Kafka connector: The timestamp.string.format configuration property

A format pattern combines units (letter codes for date/time components) with literal characters that match your input. For example, yyyy-MM-dd HH:mm:ss parses 2024-03-15 14:30:45. Units are case-sensitive.

See Working with time zones for more on timestamp handling in QuestDB.

Common format patterns

Here are practical examples of complete format strings for common use cases:

SELECT
  to_timestamp('2024-03-15T14:30:45.123456Z', 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ') as iso,
  to_timestamp('2024-03-15 14:30:45', 'yyyy-MM-dd HH:mm:ss') as standard,
  to_timestamp('15/03/2024 14:30:45', 'dd/MM/yyyy HH:mm:ss') as european
FROM long_sequence(1);

Variable-width year parsing with y

Use y when your input data has years of varying lengths. Unlike yyyy which expects exactly 4 digits, y reads all consecutive digits until it encounters a non-digit character (such as - or /).

Special case for 2-digit years: When the input contains exactly 2 digits, QuestDB interprets it as a year in the current century (2000-2099). All other lengths are interpreted literally.

For most use cases, prefer yyyy for explicit 4-digit year matching.

Parsing fractions of a second

Sub-second precision uses three unit types, each representing 3 decimal places:

Fixed-width formats use repeated letters (SSS, UUU, NNN) and expect an exact number of digits:

Variable-width formats use a single letter or + suffix to accept varying lengths:

Practical recommendations:

• For microsecond timestamps (QuestDB default): use .SSSUUU or .U+
• For nanosecond timestamps: use .SSSUUUNNN or .N+
• For millisecond-only data: use .SSS or .S

See also

• TICK interval syntax - Declarative time intervals for filtering
• Timestamps and timezones - Working with time zones
• SAMPLE BY - Time-series aggregation
• Designated timestamp - Required for interval scan optimization