Functions for date and time

Functions for date and time are functions for working with data of types DATE, TIME, TIMESTAMP and TIMESTAMPTZ.

Date and time part specifiers

The following date and time part specifiers can be used as separate functions that return the corresponding date and time parts:

  • century

  • day

  • decade

  • hour

  • microsecond

  • millennium

  • millisecond

  • minute

  • month

  • quarter

  • second

  • year

Examples:

SELECT
    century(now())     AS "current century",
    day(now())         AS "current day",
    decade(now())      AS "current decade",
    hour(now())        AS "current hour",
    microsecond(now()) AS "current microsecond",
    millennium(now())  AS "current millennium",
    millisecond(now()) AS "current millisecond",
    minute(now())      AS "current minute",
    month(now())       AS "current month",
    quarter(now())     AS "current quarter",
    second(now())      AS "current second",
    year(now())        AS "current year";
+-----------------+-------------+----------------+--------------+---------------------+--------------------+---------------------+----------------+---------------+-----------------+----------------+--------------+
| current century | current day | current decade | current hour | current microsecond | current millennium | current millisecond | current minute | current month | current quarter | current second | current year |
+-----------------+-------------+----------------+--------------+---------------------+--------------------+---------------------+----------------+---------------+-----------------+----------------+--------------+
| 21              | 21          | 202            | 15           | 43089698            | 3                  | 43089               | 5              | 5             | 2               | 43             | 2026         |
+-----------------+-------------+----------------+--------------+---------------------+--------------------+---------------------+----------------+---------------+-----------------+----------------+--------------+

current_date()

Description

Returns the current date as a value of type DATE.
Usage

current_date()
Aliases

today()
See example 
SELECT
    current_date() AS cur_date;
+------------+
| cur_date   |
+------------+
| 2025-03-02 |
+------------+

current_localtimestamp()

Description

Returns the current time and date as a value of type TIMESTAMP.
Usage

current_localtimestamp()
See example 
SELECT
    current_localtimestamp() AS now;
+----------------------------+
| now                        |
+----------------------------+
| 2025-03-02 12:38:05.014000 |
+----------------------------+

current_time()

Description

Returns the current time as a value of type TIME.
Usage

current_time
Aliases

get_current_time()
See examples 
SELECT
    current_time       AS cur_time_1,
    get_current_time() AS cur_time_2;
+-----------------+-----------------+
|    cur_time_1   |    cur_time_2   |
+-----------------+-----------------+
| 10:33:24.016000 | 10:33:24.016000 |
+-----------------+-----------------+

dayname()

Description

Returns the English name of the day of the week for a date.
Usage

dayname(<date>)
See examples 
SELECT
    dayname(DATE '2024-02-29')               AS result_1,
    dayname(TIMESTAMP '2024-02-29 00:00:00') AS result_2,
    dayname(current_localtimestamp())        AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| Thursday | Thursday | Monday   |
+----------+----------+----------+

datepart()

Description

Returns the specified part of the date or time value as a value of type BIGINT.
Usage

datepart('<part>', (TIME | DATE | ...) <date_time>).
Aliases

date_part()

Arguments can be values of types: TIME, DATE, TIMESTAMP, or TIMESTAMPTZ.

Parts can be specified using the following literals

  • century

  • day

  • decade

  • hour

  • microseconds

  • millennium

  • milliseconds

  • minute

  • month

  • quarter

  • second

  • year

See examples 
SELECT
    datepart('milliseconds', TIMESTAMP '2025-02-25 00:00:00.1') AS milliseconds,
    datepart('hour',         TIME '2025-02-25 00:00:00')        AS hour,
    datepart('millennium',   DATE '2025-02-25')                 AS millennium;
+--------------+------+------------+
| milliseconds | hour | millennium |
+--------------+------+------------+
| 100          | 0    | 3          |
+--------------+------+------------+

date_diff()

Description

Returns the number of time units between two points in time as a value of type BIGINT.
Usage

date_diff('<part>', start, end)

Aliases

datediff

Arguments can be values of type: TIME, DATE, TIMESTAMP, or TIMESTAMPTZ.

Units can be specified using the following literals

  • century

  • day

  • decade

  • hour

  • microseconds

  • millennium

  • milliseconds

  • minute

  • month

  • quarter

  • second

  • year

See examples 
SELECT
    date_diff('second', TIME '01:02:03', TIME '03:02:01')                                 AS diff_in_seconds,
    date_diff('minute', TIME '01:02:03', TIME '03:02:01')                                 AS diff_in_minutes,
    date_diff('hour',   TIME '01:02:03', TIME '03:02:01')                                 AS diff_in_hours,
    date_diff('day',    TIMESTAMP '2025-02-25 01:02:03', TIMESTAMP '2025-02-26 03:02:01') AS diff_in_days_1,
    date_diff('day',    TIMESTAMP '2025-02-26 01:02:03', TIMESTAMP '2025-02-25 03:02:01') AS diff_in_days_2,
    date_diff('day',    DATE '2024-02-27', DATE '2025-02-27')                             AS diff_in_days_3,
    date_diff('year',   DATE '0753-04-21 (BC)', now())                                    AS SPQR;
+-----------------+-----------------+---------------+----------------+----------------+----------------+------+
| diff_in_seconds | diff_in_minutes | diff_in_hours | diff_in_days_1 | diff_in_days_2 | diff_in_days_3 | SPQR |
+-----------------+-----------------+---------------+----------------+----------------+----------------+------+
| 7198            | 120             | 2             | 1              | -1             | 366            | 2778 |
+-----------------+-----------------+---------------+----------------+----------------+----------------+------+

date_trunc()

Description

Reduces a moment in time to the specified precision.
Usage

date_trunc('<part>', <time_stamp>)

Reduces the TIMESTAMP time moment to the specified precision unit and returns the initial time moment for that unit as a value of type TIMESTAMP or DATE.

Some usage examples are described in this script.

Units can be specified using the following literals

  • century

  • day

  • decade

  • hour

  • microseconds

  • millennium

  • milliseconds

  • minute

  • month

  • quarter

  • second

  • year

See examples 
SELECT
    date_trunc('minute',    TIMESTAMP '2025-02-25 01:02:03') AS minute,
    date_trunc('hour',      TIMESTAMP '2025-02-25 01:02:03') AS hour,
    date_trunc('day',       TIMESTAMP '2025-02-25 01:02:03') AS day,
    date_trunc('month',     TIMESTAMP '2025-02-25 01:02:03') AS month,
    date_trunc('quarter',   TIMESTAMP '2025-02-25 01:02:03') AS quarter,
    date_trunc('year',      TIMESTAMP '2025-02-25 01:02:03') AS year;
+---------------------+---------------------+------------+------------+------------+------------+
| minute              | hour                | day        | month      | quarter    | year       |
+---------------------+---------------------+------------+------------+------------+------------+
| 2025-02-25 01:02:00 | 2025-02-25 01:00:00 | 2025-02-25 | 2025-02-01 | 2025-01-01 | 2025-01-01 |
+---------------------+---------------------+------------+------------+------------+------------+

dayofmonth()

Description

Returns the number of the month for the given date.
Usage

dayofmonth(<date>)
Aliases

day

Returns the number of the month (the number of the day in the month starting with 1) by the given date.

See examples 
SELECT
    dayofmonth(DATE '2024-02-29')  AS result_1,
    dayofmonth(now())              AS result_2,
    dayofmonth(to_timestamp(0))    AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 29       | 21       | 1        |
+----------+----------+----------+

dayofweek()

Description

Returns the day of the week number for a date starting on Sunday.
Usage

dayofweek(<date>)
Aliases

weekday

Day numbering starts with 0: Sunday = 0, Saturday = 6.

For ISO standard numbering (starting on Monday and from 1) use isodow.
See examples 
SELECT
    dayofweek(DATE '2024-02-29')               AS result_1,
    dayofweek(TIMESTAMP '2024-02-29 00:00:00') AS result_2,
    dayofweek(current_localtimestamp())        AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 4        | 1        |
+----------+----------+----------+

dayofyear()

Description

Returns the day number of the year for a date.
Usage

dayofyear(<date>)
The numbering starts with 1.
See examples 
SELECT
    dayofyear(DATE '2024-02-29')               AS result_1,
    dayofyear(DATE '2024-12-31')               AS result_2,
    dayofyear(DATE '2025-12-31')               AS result_3,
    dayofyear(TIMESTAMP '2024-01-01 00:00:00') AS result_4,
    dayofyear(current_localtimestamp())        AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| 60       | 366      | 365      | 1        | 61       |
+----------+----------+----------+----------+----------+

epoch()

Description

Calculates the number of seconds since the start of an epoch at the given point in time.
Usage

epoch(<date>)

The beginning of an epoch is considered to be 1970-01-01.

Inverse function: to_timestamp()
See examples 
SELECT
    epoch(now())                                   AS result_1,
    epoch(TIMESTAMP '1970-01-01')                  AS result_2,
    epoch(TIMESTAMPTZ '2001-09-09T05:46:40+04:00') AS result_3;
+-------------------+----------+------------+
| result_1          | result_2 | result_3   |
+-------------------+----------+------------+
| 1779358302.018493 | 0        | 1000000000 |
+-------------------+----------+------------+

era()

Description

Returns the era number for the given date.
Usage

era(<date>)

Returns the era number (0 — before our era; 1 — our era) by the given date.

See examples 
SELECT
    era(now())                  AS result_1,
    era(DATE '0753-04-21 (BC)') AS SPQR;
+----------+------+
| result_1 | SPQR |
+----------+------+
| 1        | 0    |
+----------+------+

greatest()

Description

Returns the largest of the specified arguments.
Usage

greatest(argument[, argument, ...])

  • When used with numbers, returns the largest number.

  • When used with strings — returns the last of the strings in alphabetical order. In this case, upper case precedes lower case.

  • When used with types of date and time — returns the most recent object.

See examples 
SELECT
    greatest(1, 2, 3, 4, 4) AS result_1,
    greatest(1, -1)         AS result_2,
    greatest(0)             AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 1        | 0        |
+----------+----------+----------+
SELECT
    greatest('a', 'z', 'A', 'Z') AS result_1,
    greatest('Tengri', 'TNGRi')  AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| z        | Tengri   |
+----------+----------+
SELECT
    greatest(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    greatest(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    greatest(DATE '2024-02-29', now())                           AS result_3;
+---------------------+------------+----------------------------------+
| result_1            | result_2   | result_3                         |
+---------------------+------------+----------------------------------+
| 2024-02-29T00:00:01 | 2029-02-24 | 2026-05-06T13:05:46.173330+03:00 |
+---------------------+------------+----------------------------------+

isodow()

Description

Returns the day of the week number for a date starting on Monday.
Usage

isodow(<date>)

The numbering of days starts with 1: Monday = 1, Sunday = 7.

See examples 
SELECT
    isodow(DATE '2024-02-29')               AS result_1,
    isodow(TIMESTAMP '2024-02-29 00:00:00') AS result_2,
    isodow(current_localtimestamp())        AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 4        | 1        |
+----------+----------+----------+

isoyear()

Description

Returns the year number for an ISO standard date.
Usage

isoyear(<date>)

Returns the year number for a date according to the ISO standard.

The year starts with the week that includes 4 January.

See examples 
SELECT
    isoyear(DATE '2022-01-02')  AS result_1,
    isoyear(DATE '2022-01-03')  AS result_2,
    isoyear(now())              AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2021     | 2022     | 2026     |
+----------+----------+----------+

julian()

Description

Returns the Julian date using the given date
Usage

julian(<date>)

Returns the Julian date — the number of days between the given date and the beginning of the Julian epoch.

View Examples. 
SELECT
    julian(TIMESTAMP '2025-01-01 12:00:00+00') AS result_1,
    julian(TIMESTAMP '2025-01-01 24:00:00+00') AS result_2,
    julian(now())                              AS result_3;
+-----------+----------+-------------------+
| result_1  | result_2 | result_3          |
+-----------+----------+-------------------+
| 2460677.5 | 2460678  | 2461182.627505683 |
+-----------+----------+-------------------+

least()

Description

Returns the smallest of the specified arguments.
Usage

least(argument[, argument, ...])

  • When used with numbers, returns the smallest number.

  • When used with strings — returns the first of the strings in alphabetical order. In this case, upper case precedes lower case.

  • When used with types date and time — returns the earliest object.

See examples 
SELECT
    least(1, 1, 2, 3, 4) AS result_1,
    least(1, -1)         AS result_2,
    least(0)             AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -1       | 0        |
+----------+----------+----------+
SELECT
    least(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    least(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    least(DATE '2024-02-29', now())                           AS result_3;
+---------------------+------------+---------------------------+
| result_1            | result_2   | result_3                  |
+---------------------+------------+---------------------------+
| 2024-02-29T00:00:00 | 2024-02-29 | 2024-02-29T00:00:00+03:00 |
+---------------------+------------+---------------------------+
SELECT
    least('a', 'z', 'A', 'Z') AS result_1,
    least('Tengri', 'TNGRi')  AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| A        | TNGRi    |
+----------+----------+

monthname()

Description

Returns the English name of the month for a date.
Usage

monthname(<date>)
See examples 
SELECT
    monthname(DATE '2024-02-29')               AS result_1,
    monthname(TIMESTAMP '2024-02-29 00:00:00') AS result_2,
    monthname(current_localtimestamp())        AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| February | February | March    |
+----------+----------+----------+

now()

Description

Returns the current time as a value of type TIMESTAMPTZ
Usage

now()
See example 
SELECT
    now() AS cur_time;
+----------------------------------+
| cur_time                         |
+----------------------------------+
| 2025-08-26 13:38:58.461000+00:00 |
+----------------------------------+

strptime()

Description

Converts text to a point in time using the specified format.
Usage

strptime(<string>, <format>)

Converts text to the point in time TIMESTAMP using the specified format. If the conversion fails, it generates an error.

Some usage examples are described in this script.

Format can be specified using the following expressions
Expression Description Example

%a

Abbreviated weekday name.

Sun, Mon, …

%A

Full weekday name.

Sunday, Monday, …

%b

Abbreviated month name.

Jan, Feb, …, Dec

%B

Full month name.

January, February, …

%c

ISO date and time representation

1992-03-02 10:30:20

%d

Day of the month as a zero-padded decimal.

01, 02, …, 31

%-d

Day of the month as a decimal number.

1, 2, …, 30

%f

Microsecond as a decimal number, zero-padded on the left.

000000 - 999999

%g

Millisecond as a decimal number, zero-padded on the left.

000 - 999

%G

ISO 8601 year with century representing the year that contains the greater part of the ISO week (see %V).

0001, 0002, …, 2013, 2014, …, 9998, 9999

%H

Hour (24-hour clock) as a zero-padded decimal number.

00, 01, …, 23

%-H

Hour (24-hour clock) as a decimal number.

0, 1, …, 23

%I

Hour (12-hour clock) as a zero-padded decimal number.

01, 02, …, 12

%-I

Hour (12-hour clock) as a decimal number.

1, 2, … 12

%j

Day of the year as a zero-padded decimal number.

001, 002, …, 366

%-j

Day of the year as a decimal number.

1, 2, …, 366

%m

Month as a zero-padded decimal number.

01, 02, …, 12

%-m

Month as a decimal number.

1, 2, …, 12

%M

Minute as a zero-padded decimal number.

00, 01, …, 59

%-M

Minute as a decimal number.

0, 1, …, 59

%n

Nanosecond as a decimal number, zero-padded on the left.

000000000 - 999999999

%p

Locale’s AM or PM.

AM, PM

%S

Second as a zero-padded decimal number.

00, 01, …, 59

%-S

Second as a decimal number.

0, 1, …, 59

%u

ISO 8601 weekday as a decimal number where 1 is Monday.

1, 2, …, 7

%U

Week number of the year. Week 01 starts on the first Sunday of the year, so there can be week 00. Note that this is not compliant with the week date standard in ISO-8601.

00, 01, …, 53

%V

ISO 8601 week as a decimal number with Monday as the first day of the week. Week 01 is the week containing Jan 4. Note that %V is incompatible with year directive %Y. Use the ISO year %G instead.

01, …, 53

%w

Weekday as a decimal number.

0, 1, …, 6

%W

Week number of the year. Week 01 starts on the first Monday of the year, so there can be week 00. Note that this is not compliant with the week date standard in ISO-8601.

00, 01, …, 53

%x

ISO date representation

1992-03-02

%X

ISO time representation

10:30:20

%y

Year without century as a zero-padded decimal number.

00, 01, …, 99

%-y

Year without century as a decimal number.

0, 1, …, 99

%Y

Year with century as a decimal number.

2013, 2019 etc.

%z

Time offset from UTC in the form ±HH:MM, ±HHMM, or ±HH.

-0700

%Z

Time zone name.

Europe/Amsterdam

%%

A literal % character.

%
See examples 
SELECT
    strptime('Feb 25, 2025, 01:02:03 AM', '%b %d, %Y, %H:%M:%S %p') AS timestamp_1,
    strptime('2025-02-25, 01:02:03',      '%x, %X')                 AS timestamp_2,
    strptime('01:02, 01.02.99',           '%H:%M, %d.%m.%y')        AS timestamp_3,
    strptime('01:02%PM--01.02.99',        '%H:%M%%%p--%d.%m.%y')    AS timestamp_4,
    strptime('1:1, 1.1.1',                '%-H:%-M, %-d.%-m.%-y')   AS timestamp_5,
    strptime('1',                         '%Y')                     AS timestamp_6;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| timestamp_1         | timestamp_2         | timestamp_3         | timestamp_4         | timestamp_5         | timestamp_6         |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2025-02-25 01:02:03 | 2025-02-25 01:02:03 | 1999-02-01 01:02:00 | 1999-02-01 13:02:00 | 2001-01-01 01:01:00 | 0001-01-01 00:00:00 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

timezone_hour()

Description

Returns the time zone offset of the given time instant from 0.
Usage

timezone_hour(<time_stamp>)

Returns the time zone shift of the given time instant from 0 (UTC), expressed in hours.

See examples 
SELECT
    now(),
    timezone_hour(now()) AS result;
+----------------------------------+--------+
| now                              | result |
+----------------------------------+--------+
| 2026-05-21T14:06:26.738950+03:00 | 3      |
+----------------------------------+--------+

to_timestamp()

Description

Converts seconds from the beginning of an epoch to a moment in time.
Usage

to_timestamp(num)

Converts seconds from the beginning of an epoch to a moment in time with time zone.

The beginning of the epoch is considered to be 1970-01-01.

Inverse function: epoch()
See examples 
SELECT
    to_timestamp(1779357588.000001) AS result_1,
    to_timestamp(1767214800)        AS result_2,
    to_timestamp(1)                 AS result_3;
+----------------------------------+---------------------------+---------------------------+
| result_1                         | result_2                  | result_3                  |
+----------------------------------+---------------------------+---------------------------+
| 2026-05-21T12:59:48.000001+03:00 | 2026-01-01T00:00:00+03:00 | 1970-01-01T03:00:01+03:00 |
+----------------------------------+---------------------------+---------------------------+

weekofyear()

Description

Returns the week number of the ISO standard year.
Usage

weekofyear(<date>)

Returns the number of the week in a year according to the ISO standard.

See examples 
SELECT
    weekofyear(DATE '2026-01-01')  AS result_1,
    weekofyear(DATE '2026-01-14')  AS result_2,
    weekofyear(now())              AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 3        | 21       |
+----------+----------+----------+

yearweek()

Description

Returns the year and week number of the ISO standard as a number.
Usage

yearweek(<date>)

Returns the year and a two-digit variant of the week number in the ISO standard ISO as a single number.

See examples 
SELECT
    yearweek(DATE '2026-01-01')  AS result_1,
    yearweek(DATE '2026-01-14')  AS result_2,
    yearweek(now())              AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 202601   | 202603   | 202621   |
+----------+----------+----------+

Operator +

Description

Adds the right argument to the left argument.
Usage

<num> + <num> [+ ...] or TIME + INTERVAL [+ ...].

If used with types for for date and time, adds the interval to the time value. Returns a value of type TIME.

See also add().

See examples 
SELECT
    3 + 2      AS result_1,
    3 + 2 + -1 AS result_2,
    1.1 + 1.9  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 5        | 4        | 3.0      |
+----------+----------+----------+
SELECT
    TIME '12:11:10'     + INTERVAL 3 hours AS result_time_1,
    INTERVAL '12:11:10' + TIME '1:1:1'     AS result_time_2;
+---------------+---------------+
| result_time_1 | result_time_2 |
+---------------+---------------+
| 15:11:10      | 13:12:11      |
+---------------+---------------+

Operator -

Description

Subtracts the right argument from the left argument.
Usage

<num> - <num> [-...] or TIME - INTERVAL [-...].

If used with types for for date and time, subtracts the interval from the time value. Returns a value of type TIME.

See also subtract().

See examples 
SELECT
    3 - 2      AS result_1,
    3 - 2 - +1 AS result_2,
    1.2 - 0.2  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 0        | 1.0      |
+----------+----------+----------+
SELECT
    TIME '12:11:10' - INTERVAL 3 HOUR AS result_time_1,
    TIME '12:11:10' - INTERVAL 3 HOUR - INTERVAL 1 HOUR AS result_time_2;
+---------------+---------------+
| result_time_1 | result_time_2 |
+---------------+---------------+
| 09:11:10      | 08:11:10      |
+---------------+---------------+