Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL syntax to develop data. This topic describes the date and time functions that are supported by Hologres and provides sample statements of these functions.

The following table describes the date and time functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see Date/Time Functions and Operators and Data Type Formatting Functions in PostgreSQL documentation.

Format functions

Function Return type Description Example Return value
to_char(timestamp, text) TEXT Converts a timestamp to a string.
Note You can use the to_char function to convert timestamps between the 24-hour format and the 12-hour format.
  • to_char(current_timestamp, 'HH24:MI:SS')
  • to_char(current_timestamp,'HH12:MI:SS AM')
  • to_char(time '00:30:00','HH12:MI:SS AM')
  • 18:26:33
  • 18:26:33 PM
  • 12:30:00 AM
to_date(text, text) DATE Converts a string to a date.
  • to_date('05 Dec 2000', 'DD Mon YYYY')
  • to_date('2000 12 05', 'YYYY MM DD')
2000-12-05
to_timestamp(text, text) TIMESTAMP Converts a string to a timestamp. to_timestamp('05 Dec 2000', 'DD Mon YYYY') 2000-12-05 00:00:00
to_timestamp(double precision) TIMESTAMP Converts a timestamp to a date.
Note This UNIX timestamp represents the number of seconds that have elapsed since 00:00:00 Thursday, January 1, 1970.
  • Converts a UNIX timestamp in seconds to a date.

    SELECT TO_TIMESTAMP(163280296)

  • Converts a UNIX timestamp in milliseconds to a date.

    SELECT TO_TIMESTAMP(1632802961000/1000)

  • 1975-03-06 03:38:16+08
  • 2021-09-28 12:22:41+08

Operators

Operator Return type Example Return value
+ DATE date '2001-09-28' + integer '7' 2001-10-05
TIMESTAMP date '2001-09-28' + time '03:00' 2021-09-28 03:00:00
date '2001-09-28' + interval '1 hour' 2021-09-28 01:00:00
timestamp '2001-09-28 01:00' + interval '23 hours' 2021-09-29 00:00:00
- INTEGER date '2001-10-01' - date '2001-09-28' 3
DATE date '2001-10-01' - integer '7' 2001-09-24
TIMESTAMP date '2001-09-28' - time '03:00' 2021-09-27 21:00:00
date '2001-09-28' - interval '1 hour' 2021-09-27 23:00:00
timestamp '2001-09-28 23:00' - interval '23 hours' 2021-09-28 00:00:00
* INTERVAL 21 * interval '1 day' 21 days
/ INTERVAL interval '1 hour' / double precision '1.5' 00:40:00

Current date and time functions

Function Return type Description Example Return value
current_date DATE Returns the current date. current_date 2020-05-03
current_timestamp TIMESTAMP WITH TIME ZONE Returns the start time of the current transaction.
Note The returned value remains unchanged during the current lifecycle of the transaction.
current_timestamp 2020-05-03 06:33:36.113682+08
clock_timestamp() TIMESTAMP WITH TIME ZONE Returns the current date and time.
Note The function returns a different value each time even the function is used in the same statement.
clock_timestamp() 2020-05-03 06:32:28.814918+08
localtimestamp TIMESTAMP Returns the current time that does not contain the time zone information. localtimestamp 2020-08-21 12:02:21.178031
now() TIMESTAMP WITH TIME ZONE Returns the start time of the current transaction. This function is equivalent to the transaction_timestamp() function.
Note The returned value remains unchanged during the current lifecycle of the transaction.
now() 2020-05-03 06:38:48.492168+08
statement_timestamp() TIMESTAMP WITH TIME ZONE Returns the start time of the current statement.
Note The returned value varies based on the statement of the transaction in which the function is used.
statement_timestamp() 2020-05-05 06:39:11.125957+08
timeofday() TEXT Returns the current date and time.
Note This fucntion is similar to the clock_timestamp() function. The value returned by this function is a formatted text string.
timeofday() Tue May 03 06:39:43.195368 2020 CST
transaction_timestamp() TIMESTAMP WITH TIME ZONE Returns the start time of the current transaction. The function is equivalent to the current_timestamp function.
Note The returned value remains unchanged during the current lifecycle of the transaction.
transaction_timestamp() 2020-05-03 06:40:08.023623+08

Other functions

Function Return type Description Example Return value
date_part(text, timestamp) DOUBLE PRECISION Obtains subfields from a timestamp. The function is equivalent to the extract(field from timestamp) function.
Note The input text constants are year, month, day, hour, minute, and second.
date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_trunc(text, timestamp) TIMESTAMP Truncates a timestamp to the specified precision.
Note The input text constants are year, month, day, hour, minute, and second.
date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp) DOUBLE PRECISION Obtains subfields from a timestamp.
Note The input field constants are year, month, day, hour, minute, and second.
extract(hour from timestamp '2001-02-16 20:38:40') 20
isfinite(date) BOOLEAN Checks whether a date is a finite number. A value of true is returned if the date is a finite number and a value of false is returned if the date is not a finite number. isfinite(date '2001-02-16') true
isfinite(timestamp) BOOLEAN Checks whether a timestamp is a finite number. A value of true is returned if the timestamp is a finite number and a value of false is returned if the timestamp is not a finite number. isfinite(timestamp '2001-02-16 21:28:30') true
make_date(year int, month int, day int) DATE Creates a date that consists of the year, month, and day. make_date(2013, 7, 15) 2013-07-15