Hologres:Date and time functions

TO_CHAR

• Description:

  • Converts a timestamp into a string. By default, only timestamps that describe time points in a year from 1925 to 2282 can be converted.

    TO_CHAR(TIMESTAMP|TIMESTAMPTZ, TEXT)

    Usage notes:

    • You can use this function to convert timestamps into strings in the 24-hour clock or the 12-hour clock. HH24 indicates the 24-hour clock, and HH12 indicates the 12-hour clock. By default, the 12-hour clock is used.

    • YYYY indicates the year, MM indicates the month, DD indicates the day, HH indicates the hour, MI indicates the minute, and SS indicates the second.

    • In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_char'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support date and time values in any year.

      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%.

  • Converts an integer into a string.

    TO_CHAR(INT, TEXT)

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

    TO_CHAR(DOUBLE PRECISION, TEXT)

• Return value:

  A value of the TEXT type is returned.

• Examples:

  • Convert a timestamp into a string in the 24-hour clock.

    -- 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 into a string in the 12-hour clock.

    -- 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 value of a field into a string.

    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 value of the TEXT type 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 into a string in another time zone.

    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 timestamp value of the a field is converted into a string that uses the UTC+8 time zone.

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

    • Convert the timestamp value of the a field into a string that uses the UTC-5 time zone.

      -- 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 into a string.

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

  • Convert a double-precision number into 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:

  In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_date'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support date and time values in any year.

  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 into a timestamp. By default, only strings that describe time points in a year from 1925 to 2282 can be converted.

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

    • Usage notes:

      • The return value contains +08.

      • In Hologres V1.1.31 and later, you can execute the set hg_experimental_functions_use_pg_implementation = 'to_timestamp'; or set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp'; statement before you call this function in an SQL statement. This allows the function to support date and time values in any year.

        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 into a timestamp.

      • format_mask: required. The timestamp format.

  • Converts a timestamp into a date.

    TO_TIMESTAMP(DOUBLE PRECISION)

    Note

    The UNIX timestamp represents the number of seconds that have elapsed since 00:00:00, January 1, 1970.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• Examples:

  • Convert a string into 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 string of the TEXT data type into 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 timestamp in seconds into a date.

    -- Returned result: 1975-03-06 03:38:16+08.
    SELECT TO_TIMESTAMP(163280296);

  • Convert a UNIX timestamp in milliseconds into a date.

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

MAKE_DATE

• Description: Creates a date that consists of the year, month, and day. By default, only date and time values that describe time points in a year from 1925 to 2282 are supported.

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

• Usage notes:

  Hologres V2.0.29 and later support this function. This function does not support constants as input parameters.

• Return value:

  A value of the DATE type is returned.

• Example:

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

  The following result is returned:

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

DATEADD

• Description: Adds or subtracts an interval to or from a specified date and time value based on a specified time unit. By default, only date and time values that describe time points in a year from 1925 to 2282 are supported.

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

• Usage notes:

  Hologres instances of the following versions support this function:

  • V2.0.31 to V2.1.0

  • V2.1.13 and later

  Note

  This function does not support constants as input parameters.

• Parameters:

  • d: required. The original date and time value.

  • num: required. The interval that you want to add or subtract.

  • str: required. The specified time unit. Valid values: yyyy, year, mm, month, mon, dd, day, hh, hour, mi, and ss.

• Return value:

  A value of the DATE, TIMESTAMP, or TIMESTAMPTZ type is returned.

• 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 specific number of months to a specified date and time value. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

• Parameters:

  • d: required. The original date.

  • month: required. The integer that you want to add to the original date.

• Return value:

  A value of the DATE type is returned.

• Example:

  SELECT ADD_MONTHS(current_date, 2);

  The following result is returned:

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

DATEDIFF

• Description: Calculates the difference between two dates or timestamps. By default, only date and time values that describe time points in a year from 1925 to 2282 are supported.

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

• Usage notes:

  Hologres instances of the following versions support this function:

  • V2.0.31 to V2.1.0

  • V2.1.13 and later

  Note

  Input parameters of this function cannot be all constants.

• Parameters:

  • d1: required. The first date or timestamp.

  • d2: required. The second date or timestamp.

  • str: required. The time unit based on which the time difference is calculated. Valid values: yyyy, year, mm, month, mon, dd, day, hh, hour, mi, and ss.

• Return value:

  A value of the BIGINT type is returned. If the time difference in the specified time unit is less than 1, the value 0 is returned by default.

  For example, if you use this function to calculate the difference between 2023-12-31 and 2024-01-01 based on the time unit year, the value 0 is returned.

  Note

  If you want this function to return 1 in the preceding scenario, you can execute the set hg_experimental_datediff_use_presto_impl = off; statement before the SQL statement in which the function is included.

• 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: Returns the number of months between two dates. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

  MONTHS_BETWEEN(DATE, DATE)

• Return value:

  A value of the INT type is returned.

• Examples:

  • Example 1:

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

  • Example 2:

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

NEXT_DAY

• Description: Returns the date of the first specified day of a week after a specified date. This function is compatible with Oracle. Before you use this function, you must install the Orafce extension. For more information, see Supported Oracle functions.

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

• Parameters:

  • d: required. The specified date.

  • str: required. A string that represents the day of a week. Example: Friday. This parameter can also be set to a number that represents the day of a week. The number ranges from 1 to 7. The value 1 indicates Sunday, and the value 2 indicates Monday. In a similar manner, the value 7 indicates Saturday.

• Return value:

  A value of the DATE type is returned.

• 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);

Date or time addition: the operator +

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

Date or time subtraction: the operator -

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

Date or time multiplication: the operator *

SELECT 21 * interval '3 day';

Date or time division: the operator /

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

Date and time truncation functions

CURRENT_DATE

• Description: Returns the current date.

  CURRENT_DATE

• Return value:

  A value of the DATE type is returned.

• 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 the TRANSACTION_TIMESTAMP or NOW function.

  CURRENT_TIMESTAMP

  Note

  The return value remains unchanged during the lifecycle of the transaction.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• Example:

  SELECT CURRENT_TIMESTAMP;

  The following result is returned:

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

CLOCK_TIMESTAMP

• Description: Returns the current date and time.

  clock_timestamp()

  Note

  This function returns a different value each time even the function is used in the same statement.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• Example:

  SELECT clock_timestamp();

  The following result is returned:

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

LOCALTIMESTAMP

• Description: Returns the current time that does not contain the time zone information.

  LOCALTIMESTAMP

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• 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 the TRANSACTION_TIMESTAMP or CURRENT_TIMESTAMP function.

  NOW()

  Note

  The return value remains unchanged during the lifecycle of the transaction.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• 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 varies based on the statement of the transaction in which the function is used.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• Example:

  SELECT STATEMENT_TIMESTAMP();

  The following result is returned:

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

TIMEOFDAY

• Description: Returns the current date and time. This function is similar to the CLOCK_TIMESTAMP function. The value returned by the TIMEOFDAY function is a formatted text string.

  TIMEOFDAY()

• Return value:

  A value of the TEXT type is returned.

• 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 the CURRENT_TIMESTAMP or NOW function.

  TRANSACTION_TIMESTAMP()

  Note

  The return value remains unchanged during the lifecycle of the transaction.

• Return value:

  A value of the TIMESTAMPTZ type is returned.

• Example:

  SELECT TRANSACTION_TIMESTAMP();

  The following result is returned:

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

ISFINITE

• Description:

  • Checks whether a date of the DATE type is a finite number.

    ISFINITE(DATE)

  • Checks whether a timestamp is a finite number.

    ISFINITE(TIMESTAMP)

• Return value:

  A value of the BOOLEAN type is returned. The value true (t) is returned if the date or timestamp is a finite number, and the value false (f) is returned if the date or timestamp is not a finite number.

• 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