Date/Time Functions and Operators

Extreme DPS allows you to use several functions to process date/time values.

Dates and timestamps are all comparable, and intervals can only be compared to other values of the same data type.

When comparing a timestamp without time zone or a timestamp with time zone, the former value is assumed to be given in the time zone specified by the TimeZone configuration parameter, and is rotated to UTC for comparison to the latter value (which is already in UTC internally). Similarly, a date value is assumed to represent midnight in the TimeZone zone when comparing it to a timestamp.

info

In this section, alias timestamptz is used to represent timestamp with time zone.

Date/time operators

The following table provides the date/time operators supported by Extreme DPS.

Operator	Syntax	Return type	Description	Example	Result
`+`	`date + integer` or `integer + date`	`date`	Adds a date to an integer.	`date '2021-07-30' + integer '3'`	`date '2021-08-02'`
`+`	`date + interval` or `interval + date`	`timestamp`	Adds a date to an interval.	`date '2021-07-30' + interval '6' hour`	`timestamp '2021-07-30 06:00:00'`
`+`	`interval + interval`	`interval`	Adds an interval to another.	`interval '2' day + interval '6' hour`	`interval '2 day 6 hour'`
`+`	`timestamp + interval` or `interval + timestamp`	`timestamp`	Adds a timestamp to an interval.	`timestamp '1998-06-12 10:30:50' + interval '1 day 2 hour 3 minute 4 second'`	`timestamp '1998-06-13 12:33:54'`
`+`	`timestamptz + interval` or `interval + timestamptz`	`timestamptz`	Adds a timestamp with time zone to interval.	`timestamptz '2023-05-18 10:10:10+08:00' + interval '1 day 2 hour 3 minute 4 second'`	`timestamptz '2023-05-19 12:13:14+08:00'`
`-`	`date - date`	`integer`	Subtracts a date from a date.	`date '2023-05-18' - date '2023-05-10'`	`integer '8'` (days)
`-`	`date - integer`	`date`	Subtracts an integer from a date.	`date '2023-05-18' - integer '15'`	`date '2023-05-03'`
`-`	`date - interval`	`timestamp`	Subtracts an interval from a date.	`date '2023-05-18' - interval '10' hour`	`timestamp '2023-05-03'`
`-`	`timestamp - interval`	`timestamp`	Subtracts an interval from a timestamp.	`timestamp '2023-05-18 12:34:56' - interval '12 hour 34 minute 56 second'`	`timestamp '2023-05-18 00:00:00'`
`-`	`timestamp - timestamp`	`interval`	Subtracts a timestamp from a timestamp.	`timestamp '2023-05-18 12:34:56' - timestamp '2023-05-17 00:00:00'`	`interval '1 day 12 hour 34 minute 56 second'`
`-`	`timestamptz - interval`	`timestamptz`	Subtracts an interval from a timestamp with time zone.	`timestamptz '2023-05-18 12:34:56+08:00' - interval '2' hour`	`timestamptz '2023-05-18 10:34:56+08:00'`
`-`	`timestamptz - timestamptz`	`interval`	Subtracts a timestamp with time zone from another one.	`timestamptz '2023-05-18 12:34:56+08:00' - timestamptz '2023-05-16 10:30:50+08:00'`	`interval '2 day 2 hour 4 minute 6 second'`

Date/time functions

Extreme DPS supports many date/time functions to processing date/time values. The following sections detail the syntax, return type, description, and usage examples of each of the supported functions.

CURRENT_TIMESTAMP or NOW

Returns the timestamp when the current transaction begins.

info

NOW is the alias of CURRENT_TIMESTAMP.

Syntax

CURRENT_TIMESTAMP()
NOW()

Arguments

None.

Returns

A timestamp with time zone value.

DATEDIFF

Returns the difference between the date parts of two date or timestamp expressions.

Important

This function is supported by Extreme DPS only. Hybrid DPS does not support this function.

Syntax

DATEDIFF( <datepart>, <expression1>, <expression2> )

Arguments

<datepart>

The part of each date or timestamp value to compare.

Supported parts include year, month, and hour, as well as minute, second, millisecond, or microsecond if any of the expressions to compare are timestamp.
<expressionN>

The date or timestamp expression of which the given part is to be compared.

Note that the data types of <expression1> and <expression2> can be different.

Returns

A bigint value.

Examples

>SELECT DATEDIFF(year, "0777-12-31", "1960-02-29");
1183 

MAKE_DATE

Creates a date.

Syntax

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

Arguments

<year>

The integer expression that specifies the year of the date.
<month>

The integer expression that specifies the month of the date.
<day>

The integer expression that specifies the day of the date.

Returns

A date value in the "YYYY-MM-DD" format.

Examples

>SELECT MAKE_DATE(2022, 7, 1);
2022-07-01

MAKE_INTERVAL

Creates an interval.

Syntax

MAKE_INTERVAL( [years => <years> [, months  => <months> [, weeks  => <weeks> [, days  => <days> [, hours  => <hours> [, mins  => <mins> [, secs  => <secs> ] ] ] ] ] ] ] )

Arguments

<years>

The integer expression that specifies the number of years for the interval.
<months>

The integer expression that specifies the number of months for the interval.
<weeks>

The integer expression that specifies the number of weeks for the interval.
<days>

The integer expression that specifies the number of days for the interval.
<hours>

The integer expression that specifies the number of hours for the interval.
<mins>

The integer expression that specifies the number of minutes for the interval.
<secs>

The double precision expression that specifies the number of seconds for the interval.

The default value for each of the previous argument is 0.

Returns

An interval value.

If you leave any arguments unspecified, the default value 0 will be used. If no arguments are specified, the returned result will be an interval value with 0 seconds.

Examples

>SELECT MAKE_INTERVAL();
0 years 0 mons 0 days 0 hours 0 mins 0.0 secs

>SELECT MAKE_INTERVAL(60, 0, 10, 5);
60 years 0 mons 75 days 0 hours 0 mins 0.0 secs

> SELECT MAKE_INTERVAL(60, 0, 10, secs => 5) ;
60 years 0 mons 70 days 0 hours 0 mins 5.0 secs

MAKE_TIMESTAMP

Creates a timestamp without time zone.

Syntax

MAKE_TIMESTAMP(<year>, <month>, <day>, <hour>, <min>, <sec>)

Arguments

<year>

The integer expression that specifies the year. The valid value ranges from 1 to 9999.
<month>

The integer expression that specifies the month. The valid value ranges from 1 to 12 which respectively specifies January to December.
<day>

The integer expression that specifies the day. The valid value ranges from 1 to 31.
<hour>

The integer expression that specifies the hour. The valid value ranges from 0 to 23.
<min>

The integer expression that specifies the minute. The valid value ranges from 0 to 59.
<sec>

The double precision expression that specifies the second. The valid value ranges from 0 to 60.

Returns

A timestamp value.

If the input value of any argument is out of the valid range, an error will be reported.

Examples

>SELECT MAKE_TIMESTAMP(2022 , 10 , 5 , 12 , 34 , 56.123456);
2022-07-05T12:34:56.123Z

>SELECT MAKE_TIMESTAMP(2022, 11, 12 , 0, 0, 0);
2022-11-12T00:00Z

MAKE_TIMESTAMPTZ

Creates a timestamp with time zone.

Syntax

MAKE_TIMESTAMPTZ(<year>, <month>, <day>, <hour>, <min>, <sec>[, <timezone>])

Arguments

<year>

The integer expression that specifies the year. The valid value ranges from 1 to 9999.
<month>

The integer expression that specifies the month. The valid value ranges from 1 to 12 which respectively specifies January to December.
<day>

The integer expression that specifies the day. The valid value ranges from 1 to 31.
<hour>

The integer expression that specifies the hour. The valid value ranges from 0 to 23.
<min>

The integer expression that specifies the minute. The valid value ranges from 0 to 59.
<sec>

The double precision expression that specifies the second. The valid value ranges from 0 to 60.
<timezone>

The string expression that specifies a valid timezone. The value can be a numeric offset from UTC or a full time zone name specifying the location. For more information, see section Time zones.

If <timezone> is not specified, the current time zone will be used.

Returns

A timestamptz value.

If the input value of any argument is out of the valid range, an error will be reported.

Examples

>SELECT MAKE_TIMESTAMPTZ( 2022 , 10 , 5 , 12 , 34 , 56.123456 , 'Asia/Shanghai');
2022-10-05T04:34:56.123456Z

>SELECT MAKE_TIMESTAMPTZ( 2022 , 10 , 5 , 12 , 34 , 56.123456 , 'PST');
2022-10-05T20:34:56.123456Z

>SELECT MAKE_TIMESTAMPTZ( 2022 , 10 , 5 , 12 , 34 , 56.123456 , '+08:00');
2022-10-05T04:34:56.123456Z

TO_TIMESTAMP

Converts a Unix epoch to a timestamp with time zone.

Syntax

TO_TIMESTAMP(<epoch>)

Arguments

<epoch>: the double precision expression that specifies the Unix epoch to convert. A Unix epoch indicates the number of seconds past since 1970-01-01 00:00:00+00.

Returns

A timestamptz value.

Examples

>SELECT TO_TIMESTAMP(1584367569.12);
2020-03-16T14:06:09.120Z

CLOCK_TIMESTAMP

Returns the time when the current function starts to execute.

Syntax

CLOCK_TIMESTAMP()

Arguments

None.

Returns

A timestamptz value.

Examples

>SELECT CLOCK_TIMESTAMP();
2023-08-07T07:00:50.105348Z

STATEMENT_TIMESTAMP

Returns the time when the current statement starts to execute.

Syntax

STATEMENT_TIMESTAMP()

Arguments

None.

Returns

A timestamptz value.

Examples

>SELECT STATEMENT_TIMESTAMP();
2023-08-07T07:11:42.736373Z

DATE_TRUNC

Truncates a date/time value to a specified unit.

Syntax

DATE_TRUNC(<unit>, timestamp <timestamp>[, timezone])

DATE_TRUNC(<unit>, interval <interval>[, timezone])

Arguments

<unit>

The string expression that specifies the unit. The valid value can be:
- microseconds
- milliseconds
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- decade
- century
- millennium
<timestamp>

The timestamp expression that specifies the date/time value to truncate.

Specify this argument if you use the first syntax form.
<interval>

The INTERVAL expression that specifies the date/time value to truncate.

Specify this argument if you use the second syntax form.
<timezone>

The string expression that specifies a valid timezone. The value can be a numeric offset from UTC or a full time zone name specifying the location. For more information, see section Time zones.

If <timezone> is not specified, the current time zone will be used.

Returns

A timestamp or timestamptz value.

If the input value of any argument is out of the valid range, an error will be reported.

Examples

>SELECT DATE_TRUNC( 'microseconds', timestamp '2123-05-13 12:34:56.123456');
2123-05-13T12:34:56.123456Z

>SELECT DATE_TRUNC( 'century', timestamp '2123-05-13 12:34:56.123456');
2101-01-01T00:00Z

>SELECT DATE_TRUNC( 'microseconds', timestamp '2123-05-13 12:34:56.123456', '+8:00' );
2123-05-13T12:34:56.123456Z

EXTRACT

Extracts a subfield from a specified date/time value.

Syntax

EXTRACT(<field> from timestamp <timestamp>[, <timezone>])

EXTRACT(<field> from date <date>)

EXTRACT(<field> from interval <interval>)

Arguments

<field>

The string expression that specifies the subfield to obtain. The value can be:
- microseconds: the seconds field, including the fractional parts, multiplied by 1,000,000.
- milliseconds: the seconds field, including the fractional parts, multiplied by 1,000.
- second: the seconds field, including the fractional parts. The value is a double precision value that ranges from 0 to 60.
- minute: the minutes field. The value is an integer that ranges from 0 to 59.
- hour: the hours field. The value is an integer that ranges from 0 to 23.
- dow: The day of the week. The value is an integer that ranges from 0 (Sunday) to 6 (Saturday).
  
  This field is not supported when the specified date/time value is interval.
- isodow: the day of the week. The value is an integer that ranges from 1 (Monday) to 7 (Sunday).
  
  This field is not supported when the specified date/time value is interval.
- day: the day of the month for timestamp, timestamptz, and date, with the value range from 1 to 31; the number of days for interval.
- doy: the day of the year. The value ranges from 1 to 366.
  
  This field is not supported when the specified date/time value is interval.
- epoch: the number of seconds since 1970-01-01 00:00:00 UTC for timestamptz, the nominal number of seconds since 1970-01-01 00:00:00 for timestamp and date, or the total number of seconds in the interval for interval.
- month: the month of the year for timestamp, timestamptz, and date, with the value range from 1 to 12; or the number of months, modulo 12 for interval, with the value range from 0 to 11.
- quarter: the quarter of the year. The value ranges from 1 to 4.
  
  This field is not supported when the specified date/time value is interval.
- year: the year field.
- decade: the year field divided by 10.
- century: the century field.
- millennium: the millennium. The third millennium started January 1, 2001.
- timezone: the time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, while negative values correspond to time zones west of UTC.
  
  This field is available only when the input date/time value is a timestamptz value.
- timezone_hour: the hour component of the time zone offset.
  
  This field is available only when the input date/time value is a timestamptz value.
- timezone_minute: the minute component of the time zone offset.
  
  This field is available only when the input date/time value is a timestamptz value.
<timestamp>

The timestamp or timestamptz expression.

Specify this argument if you use the first syntax form.
<interval>

The interval expression.

Specify this argument if you use the second syntax form.
<timezone>

The string expression that specifies a valid time zone. The value can be a numeric offset from UTC or a full time zone name specifying the location. For more information, see section Time zones.

If <timezone> is not specified, the current time zone will be used.

Returns

A double precision value.

Examples

>SELECT EXTRACT(century FROM timestamp '2023-07-01 12:21:13');
21

>SELECT EXTRACT(day FROM timestamp '2023-07-01 12:21:13');
1

>SELECT EXTRACT(day FROM interval '255 days 6 minutes');
255

>SELECT EXTRACT(isodow FROM timestamp '2022-07-18 12:10:30');
1

>SELECT EXTRACT(isodow FROM date '2022-07-18');
1

FROM_UNIXTIME

Converts a UNIX timestamp to a timestamp with a timezone, using the timezone of the session.

info

A UNIX timestamp represents the number of seconds since January 1, 1970, 00:00:00 UTC.

Syntax

FROM_UNIXTIME(<unixtime>[, <zone>])
FROM_UNIXTIME(<unixtime>, <hours>, <minutes>)

Arguments

<unixtime>

The UNIX timestamp to convert.
<zone>

A placeholder field with no actual use. If specified, the system will ignore the input value.
<hours>

A placeholder field with no actual use. If specified, the system will ignore the input value.
<minutes>

A placeholder field with no actual use. If specified, the system will ignore the input value.

Returns

A timestamptz value.

Examples

SELECT FROM_UNIXTIME(1615286400) AS converted_datetime;

converted_datetime
-------------------------
2021-03-09 00:00:00.000

AT TIME ZONE

Converts a timestamp with time zone to the specified time zone.

Syntax

TIMESTAMP WITH TIME ZONE <timestamptz> AT TIME ZONE <zone>

TIMESTAMP <timestamp> AT TIME ZONE <zone>

Arguments

<timestamptz>

The timestamp with time zone to convert.

Must be specified in the TIMESTAMP WITH TIME ZONE <timestamptz> AT TIME ZONE <zone> variant.
<timestamp>

The timestamp without time zone to convert.

Must be specified in the TIMESTAMP <timestamp> AT TIME ZONE <zone> variant.
<zone>

The time zone to which the <timestamptz> is to convert.

Note that the value of <zone> must be the full spelling of the time zone, such as America/Los_Angeles. Abbreviations such as CST are not supported. Optionally, an offset strictly following "(+/-)hh:ss" the format can be specified.

Returns

A timestamp value for the TIMESTAMP WITH TIME ZONE <timestamptz> AT TIME ZONE <zone> variant.
A timestamp with time zone value for the TIMESTAMP <timestamp> AT TIME ZONE <zone> variant.

Examples

SELECT TIMESTAMP WITH TIME ZONE '2023-12-15 12:00:00+00' AT TIME ZONE 'America/Los_Angeles';
-> 2023-12-15 12:00:00

SELECT TIMESTAMP '2023-12-15 12:00:00' AT TIME ZONE 'America/Los_Angeles';
-> 22023-12-15 20:00:00+00

Date/Time Functions and Operators

Date/time operators​

Date/time functions​

CURRENT_TIMESTAMP or NOW​

Syntax​

Arguments​

Returns​

DATEDIFF​

Syntax​

Arguments​

Returns​

Examples​

MAKE_DATE​

Syntax​

Arguments​

Returns​

Examples​

MAKE_INTERVAL​

Syntax​

Arguments​

Returns​

Examples​

MAKE_TIMESTAMP​

Syntax​

Arguments​

Returns​

Examples​

MAKE_TIMESTAMPTZ​

Syntax​

Arguments​

Returns​

Examples​

TO_TIMESTAMP​

Syntax​

Arguments​

Returns​

Examples​

CLOCK_TIMESTAMP​

Syntax​

Arguments​

Returns​

Examples​

STATEMENT_TIMESTAMP​

Syntax​

Arguments​

Returns​

Examples​

DATE_TRUNC​

Syntax​

Arguments​

Returns​

Examples​

EXTRACT​

Syntax​

Arguments​

Returns​

Examples​

FROM_UNIXTIME​

Syntax​

Arguments​

Returns​

Examples​

AT TIME ZONE​

Syntax​

Arguments​

Returns​

Examples​

Date/time operators

Date/time functions

CURRENT_TIMESTAMP or NOW

Syntax

Arguments

Returns

DATEDIFF

Syntax

Arguments

Returns

Examples

MAKE_DATE

Syntax

Arguments

Returns

Examples

MAKE_INTERVAL

Syntax

Arguments

Returns

Examples

MAKE_TIMESTAMP

Syntax

Arguments

Returns

Examples

MAKE_TIMESTAMPTZ

Syntax

Arguments

Returns

Examples

TO_TIMESTAMP

Syntax

Arguments

Returns

Examples

CLOCK_TIMESTAMP

Syntax

Arguments

Returns

Examples

STATEMENT_TIMESTAMP

Syntax

Arguments

Returns

Examples

DATE_TRUNC

Syntax

Arguments

Returns

Examples

EXTRACT

Syntax

Arguments

Returns

Examples

FROM_UNIXTIME

Syntax

Arguments

Returns

Examples

AT TIME ZONE

Syntax

Arguments

Returns

Examples