Hologres:Date and time functions

Date and time conversion functions

to_char(timestamp/timestamptz, text)

• Return value type: TEXT

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

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

  • You can use the to_char 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.

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

• Examples:

  Scenario	Sample statement	Returned result
  Convert a timestamp into a string in the 24-hour clock.	SELECT to_char(current_timestamp, 'HH24:MI:SS'); SELECT to_char(current_timestamp, 'YYYY-MM-DD');	18:26:33 2022-12-08
  Convert a timestamp into a string in the 12-hour clock.	SELECT to_char(current_timestamp, 'HH12:MI:SS AM'); SELECT to_char(time '00:30:00', 'HH12:MI:SS AM');	18:26:33 PM 12:30:00 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'); SELECT to_char(b, 'HH24:MI:SS') FROM time_test; -- Convert a value of the TEXT type into a date. SELECT to_char(to_timestamp(a, 'YYYY-MM-DD'),'YYYY-MM-DD')FROM time_test;	10:23:54 2001-09-28
  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 field a is converted into a string that uses the UTC+8 time zone. SELECT to_char(a, 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test; -- Convert the timestamp into a string that uses the UTC-5 time zone. SELECT to_char(a at time zone 'US/Eastern', 'YYYY-MM-DD HH24:MI:SS') FROM timestamptz_test;	2023-03-21 16:23:54 2023-03-21 04:23:54

to_char(int, text)

• Return value type: TEXT

• Description:

  Converts an integer into a string.

• Examples:

  Sample statement	Returned result
  SELECT to_char(125, '999');	125

to_char(double precision, text)

• Return value type: TEXT

• Description:

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

• Examples:

  Sample statement	Returned result
  SELECT to_char(125.8::real, '999D9');	125.8

to_date(text, text)

• Return value type: 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.

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

• Examples:

  Scenario	Sample statement	Returned result
  Convert a string into a date.	SELECT to_date('05 Dec 2000', 'DD Mon YYYY'); SELECT to_date('2001 03 24', 'YYYY-MM-DD');	2000-12-05 2001-03-24
  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;	2001-09-28

to_timestamp(text, text)

• Return value type: TIMESTAMPTZ

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

  • The return value contains +08.

  • In Hologres V1.1.31 and later, you can execute one of the following statements before you call this function in an SQL statement:

    set hg_experimental_functions_use_pg_implementation = 'to_timestamp';

    or

    set hg_experimental_functions_use_pg_implementation = 'to_char,to_date,to_timestamp';

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

• Examples:

  Scenario	Sample statement	Returned result
  Convert a string into a timestamp.	SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');	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;	2001-01-09 00:00:00+08

to_timestamp(double precision)

• Return value type: TIMESTAMPTZ

• Description:

  Converts a timestamp into a date.

  Note

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

• Examples:

  Scenario	Sample statement	Returned result
  Convert a UNIX timestamp in seconds into a date.	SELECT to_timestamp(163280296);	1975-03-06 03:38:16+08
  Convert a UNIX timestamp in milliseconds into a date.	SELECT to_timestamp(1632802961000/1000);	2021-09-28 12:22:41+08

Functions and operators for basic operations on date and time values

Convert date and time values based on the specified time unit and interval

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;

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;

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

Current date and time functions

SELECT current_date;

SELECT current_timestamp;

SELECT clock_timestamp();

SELECT localtimestamp;

SELECT now();

SELECT statement_timestamp();

SELECT timeofday();

SELECT transaction_timestamp();

Date and time truncation functions

last_day(date/timestamp/timestamptz)

• Return value type: DATE

• Description:

  Returns the last day of the month in which a date value falls.

  • Only date and time values that describe time points in a year from 1925 to 2282 are supported.

  • Hologres V2.0.31 and later support this function. The function does not support constants as input values.

• Examples:

  Scenario	Sample statement	Returned result
  Extract the last day of the month in which 2004-02-28 00:00:00 falls.	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;	2004-02-29

extract(field FROM timestamp)

• Return value type: DOUBLE PRECISION

• Description:

  Extracts subfields from a timestamp.

  Note

  The input constants of fields include century, day, decade, dow (the day of the week, with Sunday being 0), isodow (the day of the week, with Sunday being 7), doy (the day of the year), epoch, hour, minute, month, quarter, second, week, and year.

• Examples:

  Scenario	Sample statement	Returned result
  Extract the hour part from a timestamp.	SELECT extract(hour FROM timestamp '2001-02-16 20:38:40');	20
  Extract the minute part from the current time.	SELECT extract(minute FROM now());	55
  Extract required time information based on a field in a table.	CREATE TABLE time_test(a text); INSERT INTO time_test values ('2001-09-28 03:00:00'); -- Extract the number of seconds from 1970 to the time specified by the value of a field. SELECT extract(epoch FROM to_timestamp(a, 'YYYY-MM-DD')) FROM time_test;	978969600

date_part(text, timestamp)

• Return value type: DOUBLE PRECISION

• Description:

  Extracts subfields from a timestamp. The function is equivalent to the extract(field FROM timestamp) function.

  Note

  The input constants of fields include century, day, decade, dow (the day of the week, with Sunday being 0), isodow (the day of the week, with Sunday being 7), doy (the day of the year), epoch, hour, minute, month, quarter, second, week, and year.

• Examples:

  Scenario	Sample statement	Returned result
  Extract the hour part from a timestamp.	SELECT date_part('hour', timestamp '2001-02-16 16:38:40');	16
  Extract the number of weeks from January 1 to a specified date.	SELECT date_part('week', to_date('2022-10-11', 'YYYY-MM-DD'));	41
  Extract the number of months from January 1 to a specified date.	SELECT date_part('month', to_date('2022-10-11', 'YYYY-MM-DD'));	10

date_trunc(text,time/timestamp/timestamptz)

• Return value type: TIMESTAMP or TIMESTAMPTZ

• Description:

  Truncates a timestamp to the specified precision.

  Note

  The input text constants include century, decade, year, quarter, month, week, day, hour, minute, and second.

• Examples:

  Scenario	Sample statement	Returned result
  Truncate the specified timestamp to the hour part.	SELECT date_trunc('hour', time '12:38:40');	0 years 0 mons 0 days 12 hours 0 mins 0.0 secs
  Truncate the specified timestamp to the day part.	SELECT date_trunc('day', timestamptz'2001-02-16 20:38:40+08');	2001-02-16 00:00:00+08
  Truncate the specified timestamp to the month part.	SELECT date_trunc('month', timestamp '2001-02-16 18:38:40');	2001-02-01 00:00:00
  Truncate the current timestamp to 12:00:00 on the first day of the current month.	SELECT date_trunc('month',now()) +interval '12h';	2022-12-01 12:00:00+08
  Truncate the current timestamp to 09:00:00 on the current day.	SELECT date_trunc('day',now()) + interval '9h';	2022-12-09 09:00:00+08
  Truncate the current timestamp to the current weekday of the next week.	SELECT date_trunc('day',now()) + interval '7d';	2022-12-16 00:00:00+08
  Truncate the current timestamp to 30 minutes and 30 seconds later than the current time.	SELECT date_trunc('minute',now()) + interval '30minute 30second';	2022-12-09 11:55:30+08

isfinite(date)

• Return value type: BOOLEAN

• Description:

  Checks whether a date is a finite number. The value true is returned if the date is a finite number and the value false is returned if the date is not a finite number.

• Examples:

  Sample statement	Returned result
  SELECT isfinite(date '2001-02-16');	true

isfinite(timestamp)

• Return value type: BOOLEAN

• Description:

  Checks whether a timestamp is a finite number. The value true is returned if the timestamp is a finite number and the value false is returned if the timestamp is not a finite number.

• Examples:

  Sample statement	Returned result
  SELECT isfinite(timestamp '2001-02-16 21:28:30');	true

make_date(year int, month int, day int)

• Only date and time values that describe time points in a year from 1925 to 2282 are supported.

• Hologres V2.0.29 and later support this function. The function does not support constants as input values.

• Return value type: DATE

• Description:

  Creates a date that consists of the year, month, and day.

• Examples:

  Sample statement	Returned result
  SELECT make_date(2013, 7, 15);	2013-07-15

Common sample SQL statements

• Add several hours to the current time.

  SELECT now()+interval '2 hour';

  The following result is returned:

  ?column?
  ---------------------
  2022-12-29 13:43:58.321104+08

• Convert a date into a timestamp.

  SELECT extract(epoch FROM current_timestamp);

  The following result is returned:

  date_part
  ---------------------
  1672285506.296279

• Add a value of the DATE type and a value of the INT type.

  CREATE TABLE date_test1(
      a DATE,
      b INT );
  INSERT INTO date_test1 VALUES ('2021-09-28','12');
  SELECT a + (b || ' month')::interval FROM date_test1;   

  The following result is returned:

  ?column?
  --------------------
   2022-09-28 00:00:00

• Convert a string into a timestamp.

  SELECT to_timestamp(to_char(20211027172045,'9999-99-99 99:99:99'),'YYYY-MM-DD HH24:MI:SS');

  The following result is returned:

  to_timestamp
  ----------------------
  2021-10-27 17:20:45+08

• Extract the month part.

  SELECT extract(mon FROM now());

  The following result is returned:

  date_part
  ---------
  12

• Divide an integer by another integer.

  If a remainder exists after you divide an integer by another integer, Hologres returns an integer and discards the remainder. For example, the return value of 10/3 is 3. If the remainder needs to be displayed, you can perform an explicit data type conversion. In this example, you can convert the data type to FLOAT before calculation.

  SELECT 10/3::float;

  The following result is returned:

  ?column?
  ---------
  3.3333333333333335