Hologres:Date and time functions

TO_CHAR

• Description:

  • Converts a timestamp to a string. The supported time range is 1925 to 2282 by default.

    TO_CHAR(TIMESTAMP|TIMESTAMPTZ, TEXT)

    Usage notes:

    • This function supports both 24-hour and 12-hour formats. HH24 specifies the 24-hour format, and HH12 specifies the 12-hour format. The default is the 12-hour format.

    • Common format specifiers include: YYYY for year, MM for month, DD for day, HH for hour, MI for minute, and SS for second.

    • In Hologres V1.1.31 and later, execute one of the following statements before your SQL query to support all time ranges: set hg_experimental_functions_use_pg_implementation = 'to_char'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';.

      Note

      Using this GUC parameter reduces query performance by approximately 50%. In Hologres V1.1.42 and later, this performance impact is reduced to about 20%.

  • Converts an integer to a string.

    TO_CHAR(INT, TEXT)

  • Converts a real or double-precision number to a string.

    TO_CHAR(DOUBLE PRECISION, TEXT)

• Return value:

  Returns a TEXT value.

• Examples:

  • Convert a timestamp to 24-hour format.

    -- Returned result: 13:48:30.
    SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');
    
    -- Returned result: 2024-08-05.
    SELECT TO_CHAR(current_timestamp, 'YYYY-MM-DD');

  • Convert a timestamp to 12-hour format.

    -- Returned result: 01:50:42 PM.
    SELECT TO_CHAR(current_timestamp, 'HH12:MI:SS AM');
    
    -- Returned result: 12:30:00 AM.
    SELECT TO_CHAR(time '00:30:00', 'HH12:MI:SS AM');

  • Convert a timestamp field.

    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');
    
    -- Returned result: 10:23:54.
    SELECT TO_CHAR(b, 'HH24:MI:SS') FROM time_test;
    
    -- Convert a TEXT into a date. Returned result: 2001-09-28.
    SELECT TO_CHAR(to_timestamp(a, 'YYYY-MM-DD'),'YYYY-MM-DD')FROM time_test;

  • Convert a timestamp across time zones.

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

    • If no time zone is specified, the a column is converted to the system time zone (UTC+8 by default) and then formatted as a string.

      -- Returned result: 2023-03-21 16:23:54.
      SELECT TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;

    • Specify the US/Eastern time zone, then convert the a column to a string.

      -- Returned result: 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.

    -- Returned result: 125.
    SELECT TO_CHAR(125, '999');

  • Convert a double-precision number to a string.

    -- Returned result: 125.8.
    SELECT TO_CHAR(125.8::real, '999D9');

TO_DATE

• Description: Converts a string into a date. By default, only strings that describe dates in a year from 1925 to 2282 can be converted.

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

• Usage notes:

  Starting from Hologres V1.1.31, you can execute set hg_experimental_functions_use_pg_implementation = 'to_date'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; before the SQL statement to support all time ranges.

  Note

  If you execute one of the preceding statements to configure the GUC parameter, the query performance degrades by about 50%. In Hologres V1.1.42 and later, the query performance degrades by about 20%.

• Parameters:

  • text_date: required. The string that you want to convert.

  • format_mask: required. The date format.

• Return value:

  A value of the TEXT type is returned.

• Examples:

  • Convert a string into a date.

    -- Returned result: 2000-12-05.
    SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY');
    
    -- Returned result: 2001-03-24.
    SELECT TO_DATE('2001 03 24', 'YYYY-MM-DD');

  • Convert the value of a field of the TEXT data type into 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;

    The following result is returned:

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

TO_TIMESTAMP

• Description:

  • Converts a string to a timestamp. The supported time range is 1925 to 2282 by default.

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

    • Usage notes:

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

      • In Hologres V1.1.31 and later, execute one of the following statements before your SQL query to support all time ranges: set hg_experimental_functions_use_pg_implementation = 'to_timestamp'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';.

        Note

        Using this GUC parameter reduces query performance by approximately 50%. In Hologres V1.1.42 and later, this performance impact is reduced to about 20%.

    • Parameters:

      • text_date: The string to be converted to a timestamp.

      • format_mask: The format mask for parsing the input string.

  • Converts a Unix epoch time (seconds since 1970-01-01 00:00:00 UTC) to a timestamp.

    TO_TIMESTAMP(DOUBLE PRECISION)

• Return value:

  Returns a TIMESTAMPTZ value.

• Examples:

  • Convert a string to a timestamp.

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

    The following result is returned:

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

  • Convert a TEXT field 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;

    The following result is returned:

          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.

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

MAKE_DATE

• Description: Creates a date from year, month, and day values. The supported time range is 1925 to 2282 by default.

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

• Usage notes:

  This function is available in Hologres V2.0.29 and later. In write operations, its arguments cannot all be constants.

• Return value:

  Returns a DATE value.

• Example:

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

  The following result is returned:

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

DATEADD

• Description: Adds or subtracts a specified time interval from a date. The supported time range is 1925 to 2282 by default.

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

• Usage notes:

  This function is supported in the following Hologres versions:

  • V2.0.31 to V2.1.0

  • V2.1.13 and later

  Note

  This function does not support constant-only arguments in write operations.

• Parameters:

  • d: The base date or time value.

  • num: The number of units to add or subtract.

  • str: The time unit. Valid values include yyyy and year for year; mm, month, and mon for month;

• Return value:

  Returns a value of the same type as the input date/time (DATE, TIMESTAMP, or TIMESTAMPTZ).

• Example:

  CREATE TABLE test_dateadd (a TIMESTAMP);
  INSERT INTO test_dateadd VALUES ('2005-02-28 00:00:00');
  
  -- Add one month to the specified date and time value.
  SELECT DATEADD(a , 1, 'mm') FROM test_dateadd;

  The following result is returned:

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

ADD_MONTHS

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

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

• Parameters:

  • d: The base date.

  • month: An integer representing the number of months to add.

• Return value:

  Returns a DATE value.

• Example:

  SELECT ADD_MONTHS(current_date, 2);

  The following result is returned:

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

DATEDIFF

• Description: Calculates the time difference between two dates or timestamps based on a specified unit. The supported time range is 1925 to 2282 by default.

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

• Usage notes:

  This function is supported in the following Hologres versions:

  • V2.0.31 to V2.1.0

  • V2.1.13 and later

  Note

  This function does not support cases where all parameters are constants.

• Parameters:

  • d1: The first date or timestamp value.

  • d2: The second date or timestamp value.

  • str: The time unit for calculating the difference. Valid values include yyyy and year for year; mm, month, and mon for month; dd and day for day; hh and hour for hour; mi for minute; and ss for second.

• Return value:

  Returns a BIGINT value. By default, if the time difference is less than one complete unit, the function returns 0.

  For example, the difference between 2023-12-31 and 2024-01-01 in years is 0.

  Note

  To change this behavior and count the boundary crossing (returning 1 in the preceding scenario, run set hg_experimental_datediff_use_presto_impl = off; before your SQL query to disable this GUC parameter.

• Example:

  CREATE TABLE test_datediff (a TIMESTAMP);
  INSERT INTO test_datediff VALUES ('2005-02-28 00:00:00');
  
  -- Calculate the difference in minutes between two dates.
  SELECT DATEDIFF(a , '2005-03-02 00:00:00', 'mi') FROM test_datediff;

  The following result is returned:

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

MONTHS_BETWEEN

• Description: Calculates the number of months between two dates. This Oracle-compatible function requires the orafce extension. For more information, see Supported Oracle functions.

  MONTHS_BETWEEN(DATE, DATE)

• Return value:

  Returns an INT value.

• Examples:

  • Example 1:

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

  • Example 2:

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

NEXT_DAY

• Description: Returns the date of the first specified day of the week that follows a given date. This Oracle-compatible function requires the orafce extension. For more information, see Supported Oracle functions.

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

• Parameters:

  • d: The starting date.

  • str: A string representing the day of the week (for example, Friday), or an integer from 1 to 7, where 1 represents Sunday and 2 represents Monday.

• Return value:

  Returns a DATE value.

• Examples:

  • Example 1:

    -- Returned result: 2022-05-06.
    SELECT NEXT_DAY('2022-05-01', 'FRIDAY');

  • Example 2:

    -- Returned result: 2022-05-06.
    SELECT NEXT_DAY('2022-05-01', 5);

Add date/time: +

SELECT date '2001-09-28' + integer '7';

SELECT current_date+ integer '3 ';

SELECT to_char(current_date+ interval '1 day','yyyy-mm-dd');

SELECT date '2001-09-28' + time '03:00';

SELECT date '2001-09-28' + interval '1 hour';

SELECT now()+interval '1 day';

SELECT now()+interval '1 month';

 SELECT now()+interval '2 year';

Subtract date/time: -

SELECT date '2001-10-01' - date '2001-09-28';

SELECT date '2001-10-01' - integer '7';

SELECT date '2001-09-28' - time '03:00';

 SELECT date '2001-09-28' - interval '1 hour';

SELECT now()-interval '2 day';

Multiply interval: *

SELECT 21 * interval '3 day';

Divide interval: /

SELECT interval '1 hour' / double precision '1.5';

Date/time field extraction and truncation

CURRENT_DATE

• Description: Returns the current date at the start of the transaction.

  CURRENT_DATE

• Return value:

  Returns a DATE value.

• Example:

  SELECT CURRENT_DATE;

  The following result is returned:

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

CURRENT_TIMESTAMP

• Description: Returns the start time of the current transaction. This function is equivalent to TRANSACTION_TIMESTAMP() and NOW().

  CURRENT_TIMESTAMP

  Note

  The returned value does not change within a single transaction.

• Return value:

  Returns a TIMESTAMPTZ value.

• Example:

  SELECT CURRENT_TIMESTAMP;

  The following result is returned:

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

CLOCK_TIMESTAMP

• Description: Returns the actual current time.

  clock_timestamp()

  Note

  The returned value changes on each call, even within a single SQL statement.

• Return value:

  Returns a TIMESTAMPTZ value.

• Example:

  SELECT clock_timestamp();

  The following result is returned:

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

LOCALTIMESTAMP

• Description: Returns the start time of the current transaction, but without time zone information.

  LOCALTIMESTAMP

• Return value:

  Returns a TIMESTAMP value.

• Example:

  SELECT LOCALTIMESTAMP;

  The following result is returned:

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

NOW

• Description: Returns the start time of the current transaction. This function is equivalent to TRANSACTION_TIMESTAMP() and CURRENT_TIMESTAMP.

  NOW()

  Note

  The returned value does not change within a single transaction.

• Return value:

  Returns a TIMESTAMPTZ value.

• Example:

  SELECT NOW();

  The following result is returned:

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

STATEMENT_TIMESTAMP

• Description: Returns the start time of the current statement.

  STATEMENT_TIMESTAMP()

  Note

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

• Return value:

  Returns a TIMESTAMPTZ value.

• Example:

  SELECT STATEMENT_TIMESTAMP();

  The following result is returned:

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

TIMEOFDAY

• Description: Returns the actual current time, similar to CLOCK_TIMESTAMP(). However, TIMEOFDAY() returns the time as a formatted text string.

  TIMEOFDAY()

• Return value:

  Returns a TEXT value.

• Example:

  SELECT TIMEOFDAY();

  The following result is returned:

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

TRANSACTION_TIMESTAMP

• Description: Returns the start time of the current transaction. This function is equivalent to CURRENT_TIMESTAMP and NOW().

  TRANSACTION_TIMESTAMP()

  Note

  The returned value does not change within a single transaction.

• Return value:

  Returns a TIMESTAMPTZ value.

• Example:

  SELECT TRANSACTION_TIMESTAMP();

  The following result is returned:

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

ISFINITE

• Description:

  • Tests if a date is finite (i.e., not infinity or -infinity).

    ISFINITE(DATE)

  • Tests if a timestamp is finite.

    ISFINITE(TIMESTAMP)

• Return value:

  Returns a BOOLEAN value. Returns true (t) if the value is finite, and false (f) if it is infinite.

• Examples:

  • Example 1:

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

    The following result is returned:

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

  • Example 2:

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

    The following result is returned:

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