Hologres:Date and time functions

Type conversion functions

Date/time arithmetic functions

Date/time field extraction and truncation

Current date/time functions

Other functions

TO_CHAR

Converts a timestamp, integer, or number to a string.

Syntax

TO_CHAR(TIMESTAMP | TIMESTAMPTZ, TEXT)
TO_CHAR(INT, TEXT)
TO_CHAR(DOUBLE PRECISION, TEXT)

Return type: TEXT

Usage notes

• Supports both 24-hour (HH24) and 12-hour (HH12) formats. The default is 12-hour.

• Common format specifiers: YYYY (year), MM (month), DD (day), HH (hour), MI (minute), SS (second).

• For timestamps with time zone (TIMESTAMPTZ): if no time zone is specified, the value is converted to the system time zone (UTC+8 by default) before formatting. Use AT TIME ZONE to convert to a different time zone.

• To extend the supported time range beyond 1925–2282, see Extended time range support.

Examples

Convert a timestamp to 24-hour format:

-- Returns: 13:48:30
SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');

-- Returns: 2024-08-05
SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD');

Convert a timestamp to 12-hour format:

-- Returns: 01:50:42 PM
SELECT TO_CHAR(current_timestamp, 'HH12:MI:SS AM');

-- Returns: 12:30:00 AM
SELECT TO_CHAR(time '00:30:00', 'HH12:MI:SS AM');

Convert a TIMESTAMPTZ column — the value is converted to the system time zone (UTC+8) by default:

CREATE TABLE time_test(a TEXT, b TIMESTAMPTZ);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00', '2004-10-19 10:23:54+08');

-- Returns: 10:23:54
SELECT TO_CHAR(b, 'HH24:MI:SS') FROM time_test;

Convert a TIMESTAMPTZ column to a specific time zone using AT TIME ZONE:

CREATE TABLE timestamptz_test(a TIMESTAMPTZ);
INSERT INTO timestamptz_test VALUES ('2023-03-21 10:23:54+02');

-- Without a time zone override: converted to system time zone (UTC+8). Returns: 2023-03-21 16:23:54
SELECT TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

-- With the US/Eastern time zone. Returns: 2023-03-21 04:23:54
SELECT TO_CHAR(a AT TIME ZONE 'US/Eastern', 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

Convert an integer to a string:

-- Returns: 125
SELECT TO_CHAR(125, '999');

Convert a double-precision number to a string:

-- Returns: 125.8
SELECT TO_CHAR(125.8::real, '999D9');

TO_DATE

Converts a string to a date. Default time range: 1925–2282.

Syntax

TO_DATE(<text_date> TEXT, <format_mask> TEXT)

Parameters

Return type: DATE

Usage notes

To extend the supported time range beyond 1925–2282, see Extended time range support.

Examples

Convert a string to a date:

-- Returns: 2000-12-05
SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY');

-- Returns: 2001-03-24
SELECT TO_DATE('2001 03 24', 'YYYY-MM-DD');

Convert a TEXT column to a date:

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT TO_DATE(a, 'YYYY-MM-DD') FROM time_test;

Result:

  to_date
------------
 2001-09-28

TO_TIMESTAMP

Converts a string or a Unix epoch time to a TIMESTAMPTZ value. Default time range: 1925–2282.

Syntax

-- Convert a string to a timestamp
TO_TIMESTAMP(<text_date> TEXT, <format_mask> TEXT)

-- Convert a Unix epoch time (seconds since 1970-01-01 00:00:00 UTC) to a timestamp
TO_TIMESTAMP(DOUBLE PRECISION)

Return type: TIMESTAMPTZ

Usage notes

• The result includes the time zone offset (for example, +08).

• To extend the supported time range beyond 1925–2282, see Extended time range support.

Examples

Convert a string to a timestamp:

SELECT TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY');

Result:

      to_timestamp
------------------------
 2000-12-05 00:00:00+08

Convert a TEXT column to a timestamp:

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT TO_TIMESTAMP(a, 'YYYY-MM-DD') FROM time_test;

Result:

      to_timestamp
------------------------
 2001-09-28 00:00:00+08

Convert a Unix epoch time in seconds:

-- Returns: 1975-03-06 03:38:16+08
SELECT TO_TIMESTAMP(163280296);

Convert a Unix epoch time in milliseconds (divide by 1000 first):

-- Returns: 2021-09-28 12:22:41+08
SELECT TO_TIMESTAMP(1632802961000 / 1000);

MAKE_DATE

Creates a date from integer year, month, and day values. Default time range: 1925–2282.

Syntax

MAKE_DATE(<year> INT, <month> INT, <day> INT)

Return type: DATE

Usage notes

Available in Hologres V2.0.29 and later. In write operations, arguments cannot all be constants.

Example

-- Returns: 2013-07-15
SELECT MAKE_DATE(2013, 7, 15);

Result:

 make_date
------------
 2013-07-15

DATEADD

Adds or subtracts a time interval from a date. Default time range: 1925–2282.

Syntax

DATEADD(<d> DATE | TIMESTAMP | TIMESTAMPTZ, <num> BIGINT, <str> TEXT)

Parameters

Return type: Same as the input type (DATE, TIMESTAMP, or TIMESTAMPTZ).

Usage notes

• Available in Hologres V2.0.31–V2.1.0 and V2.1.13 and later.

• In write operations, arguments cannot all be constants.

Example

Add one month to a timestamp column:

CREATE TABLE test_dateadd (a TIMESTAMP);
INSERT INTO test_dateadd VALUES ('2005-02-28 00:00:00');

SELECT DATEADD(a, 1, 'mm') FROM test_dateadd;

Result:

       dateadd
---------------------
 2005-03-28 00:00:00

ADD_MONTHS

Adds a specified number of months to a date. This is an Oracle-compatible function that requires the orafce extension. For more information, see Supported Oracle functions.

Syntax

ADD_MONTHS(<d> DATE, <month> INT)

Parameters

Return type: DATE

Example

SELECT ADD_MONTHS(current_date, 2);

Result:

 add_months
------------
 2024-10-05

DATEDIFF

Returns the difference between two dates or timestamps in a specified unit. Default time range: 1925–2282.

Syntax

DATEDIFF(<d1> DATE | TIMESTAMP | TIMESTAMPTZ, <d2> DATE | TIMESTAMP | TIMESTAMPTZ, <str> TEXT)

Parameters

Return type: BIGINT

Usage notes

• Available in Hologres V2.0.31–V2.1.0 and V2.1.13 and later.

• In write operations, arguments cannot all be constants.

• The function uses full-unit truncation, not rounding. If the difference is less than one complete unit, it returns 0. For example, the year difference between 2023-12-31 and 2024-01-01 is 0.

• To count boundary crossings instead (returning 1 in the preceding example), run the following statement before your query:

  SET hg_experimental_datediff_use_presto_impl = off;

Example

Calculate the difference in minutes between two timestamps:

CREATE TABLE test_datediff (a TIMESTAMP);
INSERT INTO test_datediff VALUES ('2005-02-28 00:00:00');

SELECT DATEDIFF(a, '2005-03-02 00:00:00', 'mi') FROM test_datediff;

Result:

 datediff
----------
    -2880

MONTHS_BETWEEN

Returns the number of months between two dates. This is an Oracle-compatible function that requires the orafce extension. For more information, see Supported Oracle functions.

Syntax

MONTHS_BETWEEN(DATE, DATE)

Return type: INT

Examples

-- Returns: 2
SELECT MONTHS_BETWEEN('2022-01-01', '2021-11-01');

-- Returns: -2
SELECT MONTHS_BETWEEN('2021-11-01', '2022-01-01');

NEXT_DAY

Returns the date of the first specified weekday that follows a given date. This is an Oracle-compatible function that requires the orafce extension. For more information, see Supported Oracle functions.

Syntax

NEXT_DAY(<d> DATE, <str> TEXT | INT)

Parameters

Return type: DATE

Examples

-- Returns: 2022-05-06
SELECT NEXT_DAY('2022-05-01', 'FRIDAY');

-- Returns: 2022-05-06
SELECT NEXT_DAY('2022-05-01', 5);

Arithmetic operators

The following operators work with date and time values.

EXTRACT

Extracts a subfield (such as year, month, or day) from a timestamp expression. EXTRACT is equivalent to DATE_PART.

Syntax

EXTRACT(field FROM TIMESTAMP)

Return type: DOUBLE PRECISION

Usage notes

Valid values for field: century, day, decade, dow (day of week, 0=Sunday), isodow (day of week, 7=Sunday), doy (day of year), epoch, hour, minute, month, quarter, second, week, year.

Examples

Extract the hour from a timestamp:

-- Returns: 20
SELECT EXTRACT(hour FROM timestamp '2001-02-16 20:38:40');

Extract the minute from the current time:

-- Returns: 12
SELECT EXTRACT(minute FROM NOW());

Get the Unix epoch time (seconds since 1970-01-01 00:00:00 UTC):

CREATE TABLE time_test(a TEXT);
INSERT INTO time_test VALUES ('2001-09-28 03:00:00');
SELECT EXTRACT(epoch FROM to_timestamp(a, 'YYYY-MM-DD')) FROM time_test;

Result:

  date_part
------------
 1001606400

Performance-optimized extract functions

Starting from Hologres V4.0, the following functions are supported for better compatibility with ClickHouse and Doris. They have the same semantics as EXTRACT(<field> FROM timestamp) but offer better performance. These functions do not support constant-only arguments.

DATE_PART

Extracts a subfield from a timestamp. DATE_PART is equivalent to EXTRACT.

Syntax

DATE_PART(<str> TEXT, <d> TIMESTAMP)

Parameters

Return type: DOUBLE PRECISION

Examples

Get the hour from a timestamp:

SELECT DATE_PART('hour', timestamp '2001-02-16 16:38:40');

Result:

 date_part
-----------
        16

Get the ISO week number for a date:

SELECT DATE_PART('week', TO_DATE('2022-10-11', 'YYYY-MM-DD'));

Result:

 date_part
-----------
        41

Get the month number for a date:

SELECT DATE_PART('month', TO_DATE('2022-10-11', 'YYYY-MM-DD'));

Result:

 date_part
-----------
        10

DATE_TRUNC

Truncates a date or time value to a specified precision.

Syntax

DATE_TRUNC(<str> TEXT, <d> TIME | TIMESTAMP | TIMESTAMPTZ)

Parameters

Return type: TIMESTAMP or TIMESTAMPTZ (matches the input type).

Examples

Truncate a time to the hour:

SELECT DATE_TRUNC('hour', time '12:38:40');

Result:

 date_trunc
------------
 12:00:00

Truncate a timestamp to the day:

SELECT DATE_TRUNC('day', timestamptz '2001-02-16 20:38:40+08');

Result:

       date_trunc
------------------------
 2001-02-16 00:00:00+08

Truncate a timestamp to the month:

SELECT DATE_TRUNC('month', timestamp '2001-02-16 18:38:40');

Result:

     date_trunc
---------------------
 2001-02-01 00:00:00

Get the first day of the current month at 12:00:

SELECT DATE_TRUNC('month', now()) + interval '12h';

Result:

       ?column?
---------------------
 2024-08-01 12:00:00+08

Get 09:00 on the current day:

SELECT DATE_TRUNC('day', now()) + interval '9h';

Result:

       ?column?
------------------------
 2024-08-08 09:00:00+08

Get the start of the same weekday in the following week:

SELECT DATE_TRUNC('day', now()) + interval '7d';

Result:

       ?column?
------------------------
 2024-08-15 00:00:00+08

LAST_DAY

Returns the last day of the month for a specified date. Default time range: 1925–2282.

Syntax

LAST_DAY(DATE | TIMESTAMP | TIMESTAMPTZ)

Return type: DATE

Usage notes

• Available in Hologres V2.0.31–V2.1.0 and V2.1.13 and later.

• In write operations, arguments cannot all be constants.

Example

CREATE TABLE test_last_day (a TIMESTAMP);
INSERT INTO test_last_day VALUES ('2004-02-28 00:00:00');

SELECT LAST_DAY(a) FROM test_last_day;

Result:

  last_day
------------
 2004-02-29

ORACLE_LAST_DAY

Returns the last day of the month for a specified date. This is an Oracle-compatible function that requires the orafce extension. Default time range: 1925–2282. For more information, see Supported Oracle functions.

Syntax

ORACLE_LAST_DAY(DATE)

Return type: DATE

Example

SELECT ORACLE_LAST_DAY('2022-05-01');

Result:

 oracle_last_day
-----------------
 2022-05-31

TRUNC

Truncates a date or timestamp to a specified precision. This is an Oracle-compatible function that requires the orafce extension. For more information, see Supported Oracle functions.

Syntax

TRUNC(<d> DATE | TIMESTAMP [, <str> TEXT])

Parameters

Return type: DATE or TIMESTAMP

Examples

Truncate a date to the start of the year:

SELECT TRUNC('2022-05-22'::date, 'Y');

Result:

   trunc
------------
 2022-01-01

Truncate a timestamp to the start of the year:

SELECT TRUNC('2022-05-22 13:11:22'::timestamp, 'Y');

Result:

        trunc
---------------------
 2022-01-01 00:00:00

Truncate a timestamp to the start of the quarter:

SELECT TRUNC('2022-05-22 13:11:22'::timestamp, 'Q');

Result:

        trunc
---------------------
 2022-04-01 00:00:00

Truncate a timestamp to the start of the day (default):

SELECT TRUNC('2022-05-22 13:11:22'::timestamp);

Result:

        trunc
---------------------
 2022-05-22 00:00:00

ROUND

Rounds a date or timestamp to the nearest time unit. This is an Oracle-compatible function that requires the orafce extension. For more information, see Supported Oracle functions.

Syntax

ROUND(<d> DATE | TIMESTAMPTZ [, <str> TEXT])

Parameters

Return type: DATE or TIMESTAMP

Examples

Round a date to the nearest year (before July 1 — rounds down):

SELECT ROUND('2022-05-22'::date, 'Y');

Result:

   round
------------
 2022-01-01

Round a date to the nearest year (after July 1 — rounds up):

SELECT ROUND('2022-07-22'::date, 'Y');

Result:

   round
------------
 2023-01-01

Round a timestamp to the nearest year:

SELECT ROUND('2022-07-22 13:11:22'::timestamp, 'Y');

Result:

        round
---------------------
 2023-01-01 00:00:00

Round a timestamp to the nearest day (default):

SELECT ROUND('2022-02-22 13:11:22'::timestamp);

Result:

        round
---------------------
 2022-02-23 00:00:00

CURRENT_DATE

Returns the current date at the start of the transaction.

Syntax

CURRENT_DATE

Return type: DATE

Example

SELECT CURRENT_DATE;

Result:

 current_date
--------------
 2024-08-08

CURRENT_TIMESTAMP

Returns the timestamp at the start of the current transaction. Equivalent to TRANSACTION_TIMESTAMP() and NOW().

Syntax

CURRENT_TIMESTAMP

The returned value does not change within a single transaction.

Return type: TIMESTAMPTZ

Example

SELECT CURRENT_TIMESTAMP;

Result:

       current_timestamp
-------------------------------
 2024-08-08 14:55:11.006068+08

CLOCK_TIMESTAMP

Returns the actual current time.

Syntax

clock_timestamp()

Unlike CURRENT_TIMESTAMP and NOW(), the returned value changes on each call, even within a single SQL statement.

Return type: TIMESTAMPTZ

Example

SELECT clock_timestamp();

Result:

        clock_timestamp
-------------------------------
 2024-08-08 14:57:43.569109+08

LOCALTIMESTAMP

Returns the timestamp at the start of the current transaction, without time zone information.

Syntax

LOCALTIMESTAMP

Return type: TIMESTAMP

Example

SELECT LOCALTIMESTAMP;

Result:

      localtimestamp
---------------------------
 2024-08-08 15:00:59.13245

NOW

Returns the timestamp at the start of the current transaction. Equivalent to CURRENT_TIMESTAMP and TRANSACTION_TIMESTAMP().

Syntax

NOW()

The returned value does not change within a single transaction.

Return type: TIMESTAMPTZ

Example

SELECT NOW();

Result:

              now
-------------------------------
 2024-08-08 15:02:50.270501+08

STATEMENT_TIMESTAMP

Returns the timestamp at the start of the current statement.

Syntax

STATEMENT_TIMESTAMP()

The value is consistent within a single statement but differs across statements within the same transaction.

Return type: TIMESTAMPTZ

Example

SELECT STATEMENT_TIMESTAMP();

Result:

      statement_timestamp
-------------------------------
 2024-08-08 15:06:14.772939+08

TIMEOFDAY

Returns the actual current time as a formatted text string. Similar to CLOCK_TIMESTAMP(), but returns a TEXT value instead of TIMESTAMPTZ.

Syntax

TIMEOFDAY()

Return type: TEXT

Example

SELECT TIMEOFDAY();

Result:

              timeofday
-------------------------------------
 Thu Aug 08 15:08:16.599369 2024 CST

TRANSACTION_TIMESTAMP

Returns the timestamp at the start of the current transaction. Equivalent to CURRENT_TIMESTAMP and NOW().

Syntax

TRANSACTION_TIMESTAMP()

The returned value does not change within a single transaction.

Return type: TIMESTAMPTZ

Example

SELECT TRANSACTION_TIMESTAMP();

Result:

     transaction_timestamp
-------------------------------
 2024-08-08 15:11:10.329005+08

ISFINITE

Tests whether a date or timestamp is finite (not infinity or -infinity).

Syntax

ISFINITE(DATE)
ISFINITE(TIMESTAMP)

Return type: BOOLEAN — returns t if finite, f if infinite.

Examples

SELECT ISFINITE(date '2001-02-16');

Result:

 isfinite
----------
 t

SELECT ISFINITE(timestamp '2001-02-16 21:28:30');

Result:

 isfinite
----------
 t