# Timestamp, date and time functions

Timestamp, date and time functions reference documentation.

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:

| Type | Precision | Approximate Range |
| :--- | :-------- | :---------------- |
| `DATE` | milliseconds | ±2.9 million years |
| `TIMESTAMP` | microseconds | ±290,000 years |
| `TIMESTAMP_NS` | nanoseconds | ±292 years |

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](/docs/query/datatypes/overview).

:::note Designated timestamp restriction
When used as a [designated timestamp](/docs/concepts/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:

```questdb-sql
-- 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](/docs/ingestion/clients/date-to-timestamp-conversion)
reference for Python, Go, Java, JavaScript, C/C++, Rust, and C#/.NET.

---

## Function categories

### Current time

| Function | Description |
| :------- | :---------- |
| [now](#now) | Current timestamp (stable within query) |
| [now_ns](#now_ns) | Current timestamp with nanosecond precision (stable within query) |
| [sysdate](#sysdate) | Current date with millisecond precision |
| [systimestamp](#systimestamp) | Current timestamp (changes per row) |
| [systimestamp_ns](#systimestamp_ns) | Current timestamp with nanosecond precision (changes per row) |
| [today](#today-tomorrow-yesterday) | Interval for current day |
| [tomorrow](#today-tomorrow-yesterday) | Interval for next day |
| [yesterday](#today-tomorrow-yesterday) | Interval for previous day |

### Extraction

| Function | Description |
| :------- | :---------- |
| [day](#day) | Extract day of month (1-31) |
| [day_of_week](#day_of_week) | Day number (1=Monday to 7=Sunday) |
| [day_of_week_sunday_first](#day_of_week_sunday_first) | Day number (1=Sunday to 7=Saturday) |
| [days_in_month](#days_in_month) | Number of days in the month |
| [extract](#extract) | Extract any time unit from timestamp |
| [hour](#hour) | Extract hour (0-23) |
| [is_leap_year](#is_leap_year) | Check if year is a leap year |
| [micros](#micros) | Extract microseconds (0-999) |
| [millis](#millis) | Extract milliseconds (0-999) |
| [minute](#minute) | Extract minute (0-59) |
| [month](#month) | Extract month (1-12) |
| [nanos](#nanos) | Extract nanoseconds (0-999) |
| [second](#second) | Extract second (0-59) |
| [week_of_year](#week_of_year) | Week number in year |
| [year](#year) | Extract year from timestamp |

### Arithmetic

| Function | Description |
| :------- | :---------- |
| [date_trunc](#date_trunc) | Truncate timestamp to specified precision |
| [dateadd](#dateadd) | Add time period to timestamp |
| [datediff](#datediff) | Difference between timestamps |
| [timestamp_ceil](#timestamp_ceil) | Round timestamp up to unit boundary |
| [timestamp_floor](#timestamp_floor) | Round timestamp down to unit/interval boundary |

### Conversion

| Function | Description |
| :------- | :---------- |
| [to_date](#to_date) | Parse string to date |
| [to_str](#to_str) | Format timestamp as string |
| [to_timestamp](#to_timestamp) | Parse string to timestamp (microsecond) |
| [to_timestamp_ns](#to_timestamp_ns) | Parse string to timestamp (nanosecond) |
| [to_timezone](#to_timezone) | Convert timestamp to timezone |
| [to_utc](#to_utc) | Convert timestamp to UTC |

### Interval construction

| Function | Description |
| :------- | :---------- |
| [interval](#interval) | Create interval from two timestamps |
| [interval_end](#interval_end) | Extract interval upper bound |
| [interval_start](#interval_start) | Extract interval lower bound |

### Utilities

| Function | Description |
| :------- | :---------- |
| [pg_postmaster_start_time](#pg_postmaster_start_time) | Server start time (PostgreSQL compatibility) |
| [timestamp_shuffle](#timestamp_shuffle) | Generate random timestamp in range |

---

:::tip Filtering vs projection

**For filtering (WHERE clause)**: Use [TICK syntax](/docs/query/operators/tick/)
for optimized interval scans:
```questdb-sql
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:
```questdb-sql
SELECT dateadd('h', 2, ts) as shifted_time FROM trades
SELECT year(ts), month(ts) FROM trades
```

TICK syntax leverages [interval scans](/docs/concepts/deep-dive/interval-scan/)
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:**

```questdb-sql
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;
```

| hour (timestamp_ns)         | month (timestamp_ns)        | year (timestamp)            | year2 (timestamp_ns)           |
|-----------------------------|-----------------------------|-----------------------------|--------------------------------|
| 2022-03-11T22:00:00.000000Z | 2022-03-01T00:00:00.000000Z | 2022-01-01T00:00:00.000000Z | 2022-01-01T00:00:00.000000000Z |

## 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](/docs/query/operators/tick/)
(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:**

```questdb-sql title="Adding hours"
SELECT systimestamp(), dateadd('h', 2, systimestamp())
FROM long_sequence(1);
```

| systimestamp                | dateadd                     |
| :-------------------------- | :-------------------------- |
| 2020-04-17T00:30:51.380499Z | 2020-04-17T02:30:51.380499Z |

```questdb-sql title="Adding days"
SELECT systimestamp(), dateadd('d', 2, systimestamp())
FROM long_sequence(1);
```

| systimestamp                | dateadd                     |
| :-------------------------- | :-------------------------- |
| 2020-04-17T00:30:51.380499Z | 2020-04-19T00:30:51.380499Z |

```questdb-sql title="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);
```

| timestamp                | with_tz                  | without_tz               |
| :----------------------- | :----------------------- | :----------------------- |
| 2024-10-21T10:00:00.000Z | 2024-10-28T10:00:00.000Z | 2024-10-28T09:00:00.000Z |

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

```questdb-sql title="Adding months"
SELECT systimestamp(), dateadd('M', 2, systimestamp())
FROM long_sequence(1);
```

| systimestamp                | dateadd                     |
| :-------------------------- | :-------------------------- |
| 2020-04-17T00:30:51.380499Z | 2020-06-17T00:30:51.380499Z |

#### See also

- [datediff](#datediff) - Difference between timestamps
- [TICK syntax](/docs/query/operators/tick/) - 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:**

```questdb-sql title="Difference in days"
SELECT datediff('d', '2020-01-23', '2020-01-27');
```

| datediff |
| :------- |
| 4        |

```questdb-sql title="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:**

```questdb-sql title="Day of the month" demo
SELECT day(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM trades
LIMIT -1;
```

| day |
| :-- |
| 01  |

```questdb-sql title="Using in an aggregation"
SELECT day(ts), count() FROM transactions;
```

| day | count |
| :-- | :---- |
| 1   | 2323  |
| 2   | 6548  |
| ... | ...   |
| 30  | 9876  |
| 31  | 2567  |

## 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:**

```questdb-sql
SELECT to_str(ts,'EE'),day_of_week(ts) FROM myTable;
```

| day       | day_of_week |
| :-------- | :---------- |
| Monday    | 1           |
| Tuesday   | 2           |
| Wednesday | 3           |
| Thursday  | 4           |
| Friday    | 5           |
| Saturday  | 6           |
| Sunday    | 7           |

## 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:**

```questdb-sql
SELECT to_str(ts,'EE'),day_of_week_sunday_first(ts) FROM myTable;
```

| day       | day_of_week_sunday_first |
| :-------- | :----------------------- |
| Monday    | 2                        |
| Tuesday   | 3                        |
| Wednesday | 4                        |
| Thursday  | 5                        |
| Friday    | 6                        |
| Saturday  | 7                        |
| Sunday    | 1                        |

## 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:**

```questdb-sql
SELECT month(ts), days_in_month(ts) FROM myTable;
```

| month | days_in_month |
| :---- | :------------ |
| 4     | 30            |
| 5     | 31            |
| 6     | 30            |
| 7     | 31            |
| 8     | 31            |

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

```questdb-sql

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

| millennium | year | month | week | hour | second |
|------------|------|-------|------|------|--------|
| 3          | 2023 | 3     | 10   | 22   | 30     |

#### See also

- [year](#year), [month](#month), [day](#day), [hour](#hour), [minute](#minute), [second](#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:**

```questdb-sql title="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 |
|:-----|
| 15   |

```questdb-sql title="Using in an aggregation"
SELECT hour(ts), count() FROM transactions;
```

| hour | count |
| :--- | :---- |
| 0    | 2323  |
| 1    | 6548  |
| ...  | ...   |
| 22   | 9876  |
| 23   | 2567  |

## 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](/docs/query/operators/tick/)
(e.g., `$today`, `$now - 1h..$now`) which enables
[interval scan](/docs/concepts/deep-dive/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:**

```questdb-sql title="Construct an interval" demo
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') |

#### See also

- [interval_start](#interval_start) - Extract interval lower bound
- [interval_end](#interval_end) - Extract interval upper bound
- [TICK syntax](/docs/query/operators/tick/) - For filtering with optimized interval scans

## 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:**

```questdb-sql title="Extract an interval upper bound" demo
SELECT
  interval_end(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )
```

| interval_end                |
| :-------------------------- |
| 2024-10-09T11:09:47.573000Z |

## 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:**

```questdb-sql title="Extract an interval lower bound" demo
SELECT
  interval_start(
    interval('2024-10-08T11:09:47.573Z', '2024-10-09T11:09:47.573Z')
  )
```

| interval_start              |
| :-------------------------- |
| 2024-10-08T11: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:**

```questdb-sql title="Simple example" demo
SELECT year(timestamp), is_leap_year(timestamp)
FROM trades
limit -1;
```

| year | is_leap_year |
| :--- | :----------- |
| 2020 | true         |
| 2021 | false        |
| 2022 | false        |
| 2023 | false        |
| 2024 | true         |
| 2025 | false        |

## 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:**

```questdb-sql title="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);
```

| micros |
|:-------|
| 456    |

```questdb-sql title="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);
```

| micros |
| :----- |
| 456    |

```questdb-sql title="Using in an aggregation"
SELECT micros(ts), count() FROM transactions;
```

| micros | count |
| :----- | :---- |
| 0      | 2323  |
| 1      | 6548  |
| ...    | ...   |
| 998    | 9876  |
| 999    | 2567  |

## 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:**

```questdb-sql title="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    |

```questdb-sql title="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    |

```questdb-sql title="Using in an aggregation"
SELECT millis(ts), count() FROM transactions;
```

| millis | count |
|:-------| :---- |
| 0      | 2323  |
| 1      | 6548  |
| ...    | ...   |
| 998    | 9876  |
| 999    | 2567  |

## 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:**

```questdb-sql title="Minute of the hour" demo
SELECT minute(to_timestamp('2022-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM trades
LIMIT -1;
```

| minute |
| :----- |
| 43     |

```questdb-sql title="Using in an aggregation"
SELECT minute(ts), count() FROM transactions;
```

| minute | count |
| :----- | :---- |
| 0      | 2323  |
| 1      | 6548  |
| ...    | ...   |
| 58     | 9876  |
| 59     | 2567  |

## 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:**

```questdb-sql title="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    |

```questdb-sql title="Using in an aggregation"
SELECT month(ts), count() FROM transactions;
```

| month | count |
| :---- | :---- |
| 1     | 2323  |
| 2     | 6548  |
| ...   | ...   |
| 11    | 9876  |
| 12    | 2567  |

## 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:**

```questdb-sql title="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:**

```questdb-sql title="Filter records to created within last day"
SELECT created, origin FROM telemetry
WHERE created > dateadd('d', -1, now());
```

| created                     | origin |
| :-------------------------- | :----- |
| 2021-02-01T21:51:34.443726Z | 1      |

```questdb-sql title="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 |

```questdb-sql title="Query based on last minute"
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:**

```questdb-sql title="Query returns same timestamp in every row"
SELECT now_ns() FROM long_sequence(3)
```

| now_ns                         |
| :----------------------------- |
| 2021-02-01T21:51:34.443726123Z |
| 2021-02-01T21:51:34.443726123Z |
| 2021-02-01T21:51:34.443726123Z |

#### See also

- [now](#now) - Current timestamp with microsecond precision
- [systimestamp_ns](#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**

```questdb-sql
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:**

```questdb-sql title="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 |
| :----- |
| 21     |

```questdb-sql title="Using in an aggregation"
SELECT second(ts), count() FROM transactions;
```

| second | count |
| :----- | :---- |
| 0      | 2323  |
| 1      | 6548  |
| ...    | ...   |
| 58     | 9876  |
| 59     | 2567  |

## 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:**

```questdb-sql title="Insert current system date along with a value"
INSERT INTO readings
VALUES(sysdate(), 123.5);
```

| sysdate                     | reading |
| :-------------------------- | :------ |
| 2020-01-02T19:28:48.727516Z | 123.5   |

```questdb-sql title="Query based on last minute"
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:**

```questdb-sql title="Insert current system timestamp"
INSERT INTO readings
VALUES(systimestamp(), 123.5);
```

| ts                          | reading |
| :-------------------------- | :------ |
| 2020-01-02T19:28:48.727516Z | 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:**

```questdb-sql title="Insert current system timestamp_ns"
INSERT INTO readings
VALUES(systimestamp_ns(), 123.5);
```

| ts                             | reading |
| :----------------------------- | :------ |
| 2020-01-02T19:28:48.727516132Z | 123.5   |

## 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](/docs/query/operators/tick/)
(`$today`, `$tomorrow`, `$yesterday`) which enables
[interval scan](/docs/concepts/deep-dive/interval-scan/) optimization.

**Arguments:**

No arguments taken.

**Return value:**

Return value is of type `interval`.

**Examples:**

```questdb-sql title="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:**

```questdb-sql title="Using today" demo
SELECT today() as today, today('CEST') as adjusted;
```

| today                                                    | adjusted                                                 |
| :------------------------------------------------------- | :------------------------------------------------------- |
| ('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.

#### See also

- [TICK syntax](/docs/query/operators/tick/) - Use `$today`, `$tomorrow`, `$yesterday` for optimized filtering

## 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:**

```questdb-sql
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
```

| ts                             | c_nano                         | c_micro                        | c_milli                        | c_second                       | c_minute                       | c_hour                         | c_day                          | c_month                        | c_year                         |
| ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ |
| 2016-02-10T16:18:22.862145333Z | 2016-02-10T16:18:22.862145333Z | 2016-02-10T16:18:22.862146000Z | 2016-02-10T16:18:22.863000000Z | 2016-02-10T16:18:23.000000000Z | 2016-02-10T16:19:00.000000000Z | 2016-02-10T17:00:00.000000000Z | 2016-02-11T00:00:00.000000000Z | 2016-03-01T00:00:00.000000000Z | 2017-01-01T00:00:00.000000000Z |

## 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](/docs/query/sql/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:**

```questdb-sql
SELECT timestamp_floor('5d', '2018-01-01')
```

Gives:

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

The number part of the expression is optional:

```questdb-sql
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:

| ts                             | c_nano                         | c_micro                        | c_milli                        | c_second                       | c_minute                       | c_hour                         | c_day                          | c_month                        | c_year                         |
| ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ | ------------------------------ |
| 2016-02-10T16:18:22.862145333Z | 2016-02-10T16:18:22.862145333Z | 2016-02-10T16:18:22.862145000Z | 2016-02-10T16:18:22.862000000Z | 2016-02-10T16:18:22.000000000Z | 2016-02-10T16:18:00.000000000Z | 2016-02-10T16:00:00.000000000Z | 2016-02-10T00:00:00.000000000Z | 2016-02-01T00:00:00.000000000Z | 2016-01-01T00:00:00.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:

```questdb-sql
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.

```questdb-sql
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:

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

Gives:

| t                           | count   |
| --------------------------- | ------- |
| 2018-01-01T00:00:00.000000Z | 1226531 |
| 2018-01-06T00:00:00.000000Z | 1468302 |
| 2018-01-11T00:00:00.000000Z | 1604016 |
| 2018-01-16T00:00:00.000000Z | 1677303 |
| ...                         | ...     |

## 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:**

```questdb-sql
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](#timestamp-format).

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

```questdb-sql title="string matches format" demo
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 |

```questdb-sql title="string does not match format"
SELECT to_date('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);
```

| to_date |
| :------ |
| null    |

```questdb-sql title="Using with INSERT"
INSERT INTO measurements
values(to_date('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.5);
```

| date                     | value |
| :----------------------- | :---- |
| 2019-12-12T12:15:00.000Z | 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](#timestamp-format).

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

```questdb-sql
SELECT to_str(systimestamp(), 'yyyy-MM-dd') FROM long_sequence(1);
```

| to_str     |
| :--------- |
| 2020-03-04 |

- With unrecognized timestamp definition

```questdb-sql
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](#timestamp-format).

**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`](#to_timestamp_ns) if nanosecond precision is required.

**Examples:**

```questdb-sql title="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 |

```questdb-sql title="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 |

```questdb-sql title="String does not match format"
SELECT to_timestamp('2020-03-01:15:43:21', 'yyyy')
FROM long_sequence(1);
```

| to_timestamp |
| :----------- |
| null         |

```questdb-sql title="Using with INSERT"
INSERT INTO measurements
values(to_timestamp('2019-12-12T12:15', 'yyyy-MM-ddTHH:mm'), 123.5);
```

| timestamp                   | value |
| :-------------------------- | :---- |
| 2019-12-12T12:15:00.000000Z | 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](/docs/query/sql/where/#native-timestamp-format)

#### See also

- [to_timestamp_ns](#to_timestamp_ns) - Parse string to timestamp with nanosecond precision
- [to_str](#to_str) - Format timestamp as string
- [Timestamp format](#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](#timestamp-format).

**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`](#to_timestamp) instead.

**Examples:**

```questdb-sql title="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](/docs/concepts/timestamps-timezones/).

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

```questdb-sql
SELECT to_timezone(1623167145000000, 'Europe/Berlin')
```

| to_timezone                 |
| :-------------------------- |
| 2021-06-08T17:45:45.000000Z |

- Unix UTC timestamp in microseconds to PST by UTC offset

```questdb-sql
SELECT to_timezone(1623167145000000, '-08:00')
```

| to_timezone                 |
| :-------------------------- |
| 2021-06-08T07:45:45.000000Z |

- Timestamp as string to `PST`

```questdb-sql
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](/docs/concepts/timestamps-timezones/).

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

```questdb-sql
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

```questdb-sql
SELECT to_utc(1623167145000000, '-08:00')
```

| to_utc                      |
| :-------------------------- |
| 2021-06-08T23:45:45.000000Z |

- Timestamp as string in `PST` to UTC

```questdb-sql
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**

```questdb-sql
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:**

```questdb-sql title="Year"
SELECT year(to_timestamp('2020-03-01:15:43:21', 'yyyy-MM-dd:HH:mm:ss'))
FROM long_sequence(1);
```

| year |
| :--- |
| 2020 |

```questdb-sql title="Using in an aggregation"
SELECT year(ts), count() FROM transactions;
```

| year | count |
| :--- | :---- |
| 2015 | 2323  |
| 2016 | 9876  |
| 2017 | 2567  |

#### See also

- [extract](#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()`](#to_timestamp) and [`to_timestamp_ns()`](#to_timestamp_ns) for parsing text into native timestamp values
- **CSV import**: The `timestamp` parameter in [`COPY`](/docs/query/sql/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](/docs/concepts/timestamps-timezones/) for more on
timestamp handling in QuestDB.

| Unit   | Date or Time Component                                                     | Presentation       | Examples                              |
| ------ |----------------------------------------------------------------------------| ------------------ | ------------------------------------- |
| `G`    | Era designator                                                             | Text               | AD                                    |
| `y`    | `y` single digit or greedy year, depending on the number of digits in input | Year               | 1996; 96; 999; 3                      |
| `yy`   | Two digit year of the current century                                      | Year               | 96 (interpreted as 2096)              |
| `yyy`  | Three-digit year                                                           | Year               | 999                                   |
| `yyyy` | Four-digit year                                                            | Year               | 1996                                  |
| `M`    | Month in year, numeric, greedy                                             | Month              | 7; 07; 007; etc.                      |
| `MM`   | Month in year, two-digit                                                   | Month              | 07                                    |
| `MMM`  | Month in year, name                                                        | Month              | Jul; July                             |
| `w`    | Week in year                                                               | Number             | 2                                     |
| `ww`   | ISO week of year (two-digit)                                               | Number             | 02                                    |
| `D`    | Day in year                                                                | Number             | 189                                   |
| `d`    | Day in month                                                               | Number             | 10                                    |
| `F`    | Day of week in month                                                       | Number             | 2                                     |
| `E`    | Day name in week                                                           | Text               | Tuesday; Tue                          |
| `u`    | Day number of week (1 = Monday, ..., 7 = Sunday)                           | Number             | 1                                     |
| `a`    | Am/pm marker                                                               | Text               | PM                                    |
| `H`    | Hour in day (0-23)                                                         | Number             | 0                                     |
| `k`    | Hour in day (1-24)                                                         | Number             | 24                                    |
| `K`    | Hour in am/pm (0-11)                                                       | Number             | 0                                     |
| `h`    | Hour in am/pm (1-12)                                                       | Number             | 12                                    |
| `m`    | Minute in hour                                                             | Number             | 30                                    |
| `s`    | Second in minute                                                           | Number             | 55                                    |
| `SSS`  | 3-digit millisecond (see explanation below for fraction-of-second)         | Number             | 978                                   |
| `S`    | Millisecond up to 3 digits (see explanation below for fraction-of-second)  | Number             | 900                                   |
| `UUU`  | 3-digit microsecond (see explanation below for fraction-of-second)         | Number             | 456                                   |
| `U`    | Microsecond up to 3 digits (see explanation below for fraction-of-second)  | Number             | 456                                   |
| `U+`   | Microsecond up to 6 digits (see explanation below for fraction-of-second)  | Number             | 123456                                |
| `N`    | Nanosecond up to 3 digits (see explanation below for fraction-of-second)   | Number             | 900                                   |
| `N+`   | Nanosecond up to 9 digits (see explanation below for fraction-of-second)   | Number             | 123456789                             |
| `z`    | Time zone                                                                  | General time zone  | Pacific Standard Time; PST; GMT-08:00 |
| `Z`    | Time zone                                                                  | RFC 822 time zone  | -0800                                 |
| `x`    | Time zone                                                                  | ISO 8601 time zone | -08; -0800; -08:00                    |

### Common format patterns

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

| Format pattern                  | Example input                   | Description                              |
|---------------------------------|---------------------------------|------------------------------------------|
| `yyyy-MM-ddTHH:mm:ss.SSSUUUZ`   | `2024-03-15T14:30:45.123456Z`   | ISO 8601 with microseconds               |
| `yyyy-MM-ddTHH:mm:ss.SSSUUUNNN` | `2024-03-15T14:30:45.123456789` | With nanoseconds                         |
| `yyyy-MM-dd HH:mm:ss`           | `2024-03-15 14:30:45`           | Standard datetime with space separator   |
| `yyyy-MM-dd`                    | `2024-03-15`                    | Date only                                |
| `yyyy-MM-ddTHH:mm:ssZ`          | `2024-03-15T14:30:45Z`          | ISO 8601 without fractional seconds      |
| `yyyy-MM-dd HH:mm:ss.SSS`       | `2024-03-15 14:30:45.123`       | Datetime with milliseconds               |
| `dd/MM/yyyy HH:mm:ss`           | `15/03/2024 14:30:45`           | European date format                     |
| `MM/dd/yyyy HH:mm:ss`           | `03/15/2024 14:30:45`           | US date format                           |
| `yyyyMMdd-HHmmss`               | `20240315-143045`               | Compact format (often used in filenames) |
| `yyyy-MM-ddTHH:mm:ss.SSSz`      | `2024-03-15T14:30:45.123PST`    | With timezone abbreviation               |

```questdb-sql title="Parsing common formats"
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.

| Input      | Format | Result                        | Explanation                                  |
| ---------- | ------ | ----------------------------- | -------------------------------------------- |
| `5-03`     | `y-M`  | `0005-03-01T00:00:00.000000Z` | 1 digit → literal year 5                     |
| `05-03`    | `y-M`  | `2005-03-01T00:00:00.000000Z` | 2 digits → current century (20xx)            |
| `005-03`   | `y-M`  | `0005-03-01T00:00:00.000000Z` | 3 digits → literal year 5                    |
| `0005-03`  | `y-M`  | `0005-03-01T00:00:00.000000Z` | 4 digits → literal year 5                    |
| `2024-03`  | `y-M`  | `2024-03-01T00:00:00.000000Z` | 4 digits → literal year 2024                 |

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:

| Unit  | Represents   | Position in fraction |
| ----- | ------------ | -------------------- |
| `S`   | Milliseconds | Digits 1-3 (`.XXX`)  |
| `U`   | Microseconds | Digits 4-6 (`.___XXX`) |
| `N`   | Nanoseconds  | Digits 7-9 (`.______XXX`) |

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

| Format       | Digits | Example input | Parsed value          |
| ------------ | ------ | ------------- | --------------------- |
| `.SSS`       | 3      | `.123`        | 123 ms                |
| `.SSSUUU`    | 6      | `.123456`     | 123 ms + 456 µs       |
| `.SSSUUUNNN` | 9      | `.123456789`  | 123 ms + 456 µs + 789 ns |

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

| Format   | Digits | Example input | Parsed value              |
| -------- | ------ | ------------- | ------------------------- |
| `.S`     | 1-3    | `.12`         | 120 ms                    |
| `.SSSU`  | 4-6    | `.1234`       | 123 ms + 400 µs           |
| `.SSSUUUN` | 7-9  | `.1234567`    | 123 ms + 456 µs + 700 ns  |
| `.U+`    | 1-6    | `.12345`      | 123 ms + 450 µs           |
| `.N+`    | 1-9    | `.12`         | 120 ms (pads with zeros)  |

**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](/docs/query/operators/tick/) - Declarative time intervals for filtering
- [Timestamps and timezones](/docs/concepts/timestamps-timezones/) - Working with time zones
- [SAMPLE BY](/docs/query/sql/sample-by/) - Time-series aggregation
- [Designated timestamp](/docs/concepts/designated-timestamp/) - Required for interval scan optimization