Dataphin:Date functions

The string functions supported by AnalyticDB for PostgreSQL are as follows.

age

• Command syntax

age(timestamp, timestamp)

• Command description: Produces a "symbolic" result in years and months by subtracting parameters, rather than just days.

• Parameter description:

  timestamp: Required. The date to be calculated.

  timestamp: Required. Date for comparison.

• Return value description: Returns a "symbolic" result in years and months after subtracting the specified parameters from the date.

• Example:

  SELECT age(timestamp '2010-02-02', timestamp '2001-01-01');
  --Returns 9 years 1 mon 1 day

age

• Command syntax

age(timestamp)

• Command description: Subtracts the specified date from the current date.

• Parameter description:

  timestamp: Required. The date to be calculated.

• Return value description: Returns an integer representing the difference between the current date and the specified date.

• Example:

  SELECT age(timestamp '1996-05-02');
  --Returns 21 years 4 mons 12 days

clock_timestamp

• Command syntax

clock_timestamp()

• Command description: Retrieves the current date and time, which may change during the execution of a statement.

• Return value description: Returns the system date and time, including time zone information, at the moment the function is executed.

• Example:

  SELECT clock_timestamp();
  --Returns 2023-01-03 15:38:44.332799

current_date

• Command syntax

current_date

• Command description: Retrieves the current system date.

• Return value description: Returns the current system date in the format YYYY-MM-DD.

• Example:

  SELECT current_date;
  --Returns 2023-01-03

current_time

• Command syntax

current_time

• Command description: Retrieves the current system time.

• Return value description: Returns the system time with time zone information in the format HH:MI:SS.ssssss{+|-}ZZ.

• Example:

  SELECT current_time;
  --Returns 20:37:44.688689+03

current_timestamp

• Command syntax

current_timestamp

• Command description: Retrieves the current date and time at the start of the current transaction.

• Return value description: Returns the current date and time at the start of the current transaction.

• Example:

  SELECT current_timestamp;
  --Returns 2023-01-01 15:55:37.222601+03

date_part

• Command syntax

date_part(text, timestamp)

• Command description: Extracts a specified subfield, functioning similarly to the extract command.

• Parameter description:

  text: Required. String representing the part to extract.

  timestamp: Required. Can be a timestamp, date, time, or interval type.

• Return value description: Returns the part specified by the string text from the timestamp.

• Example:

  SELECT date_part('century', TIMESTAMP '2023-01-01 12:41:13.662522');
  --Returns 21

date_trunc

• Command syntax

date_trunc(text, timestamp)

• Command description: Truncates to the specified precision.

• Parameter description:

  string: Required. A string representing the part to truncate.

• Return value description: Truncates the specified time to the indicated part and returns it.

• Example:

  SELECT date_trunc('year', TIMESTAMP '2023-01-01 12:41:13.662522');
  --Returns 2023-01-01 00:00:00.0

date_trunc

• Command syntax

date_trunc(text, interval)

• Command description: Truncates to specified precision, specifically for interval type values.

• Parameter description:

  string: Required. A string representing the part to truncate.

• Return value description: Truncates the specified interval value to the indicated part and returns it.

• Example:

  SELECT date_trunc('hour', INTERVAL '1 days 20:10:10');
  --Returns 1 day 20:00:00

extract

• Command syntax

extract(field from timestamp)

• Command description: Extracts a specified subfield.

• Parameter description:

  field: Required. A string representing the part to extract.

  timestamp: Required. A timestamp.

• Return value description: Truncates the specified timestamp or interval value to the indicated part and returns it.

• <

  Example:

  SELECT extract(CENTURY FROM TIMESTAMP '2023-01-01 12:41:13.662522');
  --Returns 21

extract

• Command syntax

extract(field from interval)

• Command description: Extracts a specified subfield.

• Parameter description:

  field: Required. A string representing the part to extract.

  timestamp: Required. An interval value.

• Return value description: Truncates the specified timestamp or interval value to the indicated part and returns it.

• Example:

  SELECT extract(DAY FROM INTERVAL '2 days 10 minutes');
  --Returns 2

isfinite

• Command syntax

isfinite(date)

• Command description: Tests for a finite date (not +/-infinity).

• Parameter description:

  string: Required. A string whose digits need to be calculated.

• Return value description: Returns an integer representing the number of digits in the given string.

• Example:

  SELECT isfinite(DATE '2023-01-01');
  --Returns t

isfinite

• Command syntax

isfinite(timestamp)

• Command description: Tests for a finite timestamp (not +/-infinity).

• Parameter description:

  timestamp: Required. The timestamp value to be detected.

• Return value description: Returns a Boolean value indicating whether the given date, timestamp, or interval value is finite.

• Example:

  SELECT isfinite(TIMESTAMP '2023-01-01 12:41:13.662522');
  --Returns t

isfinite

• Command syntax

isfinite(interval)

• Command description: Tests for a finite interval value (not +/-infinity).

• Parameter description:

  interval: Required. The interval value to be detected.

• Return value description: Returns a Boolean value indicating whether the given date, timestamp, or interval value is finite.

• Example:

  isfinite(INTERVAL '2 days 10 minutes');
  --Returns t

justify_days

• Command syntax

justify_days(interval)

• Command description: Adjusts intervals to represent 30-day periods as months.

• Parameter description:

  interval: Required. The interval value to be transformed.

• Return value description: Returns an integer representing the number of digits in the given string.

• Example:

  SELECT justify_days(INTERVAL '20 days');
  --Returns 20 days

justify_hours

• Command syntax

justify_hours(interval)

• Command description: Adjusts intervals to represent 24-hour periods as days.

• Parameter description:

  interval: Required. The interval value to be transformed.

• Return value description: Converts hours exceeding 24 hours into days within the interval, without altering intervals less than 24 hours.

• Example:

  SELECT justify_hours(INTERVAL '20 hours');
  --Returns 20:00:00

justify_interval

• Command syntax

justify_interval

• Command description: Adjusts intervals using justify_days and justify_hours, including sign adjustments.

• Parameter description:

  string: Required. The interval value to be transformed.

• Return value description: Converts representation of time intervals, adjusting hours to days and days to months where applicable.

• Example:

  SELECT justify_interval(INTERVAL '6000 hours');
  --Returns 8 mons 10 days

localtime

• Command syntax

localtime

• Command description: Retrieves the current time.

• Return value description: Returns the current system time in the format HH:MM:SS.ssssss.

• Example:

  SELECT localtime;
  --Returns 64

localtimestamp

• Command syntax

localtimestamp

• Command description: Retrieves the current date and time at the start of the current transaction.

• Return value description: Returns the system date and time at the start of the transaction to which the function belongs.

• Example:

  SELECT localtimestamp;
  --Returns 16:43:04.582222

make_date

• Command syntax

make_date(year, month, day)

• Command description: Creates a date from year, month, and day fields.

• Parameter description:

  year: Integer for the year.

  month: Integer for the month.

  day: Integer for the day.

• Return value description: Returns a date value created from the specified year, month, and day fields. Negative years indicate dates BC.

• Example:

  SELECT make_date(2023, 01, 01);
  --Returns 2023-01-01

make_interval

• Command syntax

make_interval

• Command description: Creates an interval from year, month, week, day, hour, minute, and second fields.

• Return value description: Returns an interval value created from the specified fields.

• Example:

  SELECT make_interval(1, 2, 3, 4, 5, 6, 1.123);
  --Returns 1 year 2 mons 25 days 05:06:01.123

make_time

• Command syntax

make_time(hour, minute, second)

• Command description: Creates a time from hour, minute, and second fields.

• Parameter description:

  hour: Required. Integer for the hour.

  minute: Required. Integer for the minute.

  second: Required. Floating-point number for the second.

• Return value description: Returns a time value created from the specified hour, minute, and second fields.

• Example:

  SELECT make_time(20, 55, 25.517606);
  --Returns 20:55:25.517606

make_timestamp

• Command syntax

make_timestamp(year, month, day, hour, minute, second)

• Command description: Creates a timestamp from year, month, day, hour, minute, and second fields.

• Parameter description:

  Parameter Description:

  year: Mandatory. Specifies the year as an integer.

  month: Required. Integer for the month.

  day: Required. Integer for the day.

  hour: Required. Integer for the hour.

  minute: Required. Integer for the minute.

• Description of return value: This function generates a timestamp from the specified year, month, day, hour, minute, and second. A negative year signifies a BC date.

• Example:

  SELECT make_timestamp(2023, 01, 01, 20, 55, 25.517606);
  --Returns 2023-01-01 20:55:25.517606

make_timestamptz

• Command syntax

make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])

• Command description: Creates a timestamp with time zone from year, month, day, hour, minute, and second fields, defaulting to the current time zone if unspecified.

• Parameter description:

  Description of Parameters:

  year: Mandatory. Specifies the integer value for the year.

  month: Required. Integer for the month.

  day: Required. Integer for the day.

  hour: Required. Integer for the hour.

  minute: Required. Integer for the minute.

  timezone: Optional. A string representing the time zone, with the current time zone as the default.

• Return value description: This function yields a timestamp with time zone, assembled from specified year, month, day, hour, minute, second, and time zone fields. A negative year signifies a BC date.

• Example:

  SELECT make_timestamptz(2023, 01, 01, 20, 55, 25.517606);
  --Returns 2023-01-01 20:55:25.517606+08

now

• Command syntax

now()

• Command description: Retrieves the current date and time, corresponding to the system date and time at the start of the transaction to which it belongs.

• Return value description: Returns the date and time with time zone information, corresponding to the system date and time at the start of the transaction to which the function belongs.

• Example:

  SELECT now();
  --Returns 2023-01-03 16:05:27.182015

statement_timestamp

• Command syntax

statement_timestamp()

• Command description: Retrieves the current date and time at the start of the current statement.

• Return value description: Returns the date and time with time zone information, corresponding to the system date and time at the start of the statement to which the function belongs.

• Example:

  SELECT statement_timestamp();
  --Returns 2023-01-03 16:08:27.273888

timeofday

• Command syntax

timeofday()

• Command description: Retrieves the current date and time, similar to clock_timestamp, but formatted as a text string.

• Return value description: Returns a text string representation of the system date and time when the function is executed, including time zone information.

• Example:

  SELECT timeofday();
  --Returns Tue Jan 03 16:08:09.228504 2023 CST

transaction_timestamp

• Command syntax

transaction_timestamp()

• Command description: Retrieves the current date and time at the start of the transaction to which it belongs.

• Return value description: Returns the date and time with time zone information, corresponding to the system date and time at the start of the transaction to which the function belongs.

• Example:

  SELECT transaction_timestamp();
  --Returns 2023-01-03 16:08:06.276571