Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ Data Types/ Date/Time Types
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Date/Time Types

Table 1 lists the date/time types that can be used in GaussDB. For the operators and built-in functions of the types, see Date and Time Processing Functions and Operators.

If the time format of another database is different from that of GaussDB, modify the value of the DateStyle parameter to keep them consistent.

Table 1 Date/Time types

Name

Description

Storage Space

DATE

Date and time.

Minimum value: 4713-01-01BC (4713 B.C.). Maximum value: 5874897-12-31AD (5874897 A.C.)

After sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', the input and output formats and ranges are different.

  • Input format: YYYY-MM-DD with separators and YYYYMMDD without separators are supported.
    • In the scenario where separators are used, the plus sign (+) and colon (:) cannot be used as separators between the year, month, and day of a date. (Some separators are not supported when they are used together. The specifications inherit the existing implementation. Example: date'2020-12?12'.) The input format is not affected by the DateStyle parameter.
    • In the scenario where separators are not used, the output result may be incorrect though no error is reported.
  • Output format: Only YYYY-MM-DD is supported and the format is not affected by the DateStyle parameter.
  • Value range: 4713-01-01 BC to 5874897-12-31 AD.
    • If the value of sql_mode contains strict_trans_tables, an error is reported if the value is invalid or exceeds the range.
    • If the value of sql_mode does not contain strict_trans_tables, the values of year, month, and day can be 0. However, the values will be converted to valid values in the sequence of year, month, and day. For example, '0000-00-10' will be converted to 0002-12-10 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 00:00:00 is returned.
NOTE:

For ORA compatibility, the database treats empty strings as NULL and replaces DATE with TIMESTAMP(0) WITHOUT TIME ZONE.

4 bytes (8 bytes in ORA-compatible mode)

TIME [(p)] [WITHOUT TIME ZONE]

Time within one day (without a time zone).

p indicates the precision after the decimal point. The value ranges from 0 to 6.

Minimum value: 00:00:00. Maximum value: 24:00:00.

After sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', they are not limited to the time in a day. For example, they can indicate concepts such as duration and relative time, and the format, range, and precision are different.

  • Input format: [D] hh:mm:ss.ffffff with separators and hhmmss.ffffff without separators are supported. D indicates the number of days. The value is an integer (negative numbers are supported). This parameter is optional. The value is converted into the number of hours on the basis of 24 hours in a day. The value is displayed after hh is added. Only colons (:) can be used as separators.
  • Output format: hh:mm:ss.ffffff. All zeros at the end of the decimal part are automatically ignored during display.
  • Value range: –838:59:59.000000 to 838:59:59.000000.
    • If the value of sql_mode contains strict_trans_tables, 00:00:00 is returned for invalid input. If the value exceeds the range, an error is reported.
    • If the value of sql_mode does not contain strict_trans_tables, 00:00:00 is returned for invalid input. If the input is valid but exceeds the range, the nearest boundary value is returned. For example, inputting 838:59:59.000001 returns 838:59:59, and inputting –838:59:59.000001 returns –838:59:59.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If p is set to a value greater than 6, the value 6 is used.
    • When it is used as the data type of a table column, the default precision is 0.
    • When it is used as an expression (for example, time '10:10:10.123456'), the default precision is 6.

8 bytes

TIME [(p)] [WITH TIME ZONE]

Time within one day (with a time zone).

p indicates the precision after the decimal point. The value ranges from 0 to 6.

Minimum value: 00:00:00+15:59. Maximum value: 24:00:00–15:59.

12 bytes

TIMESTAMP[(p)] [WITHOUT TIME ZONE]

Date and time (without a time zone).

p indicates the precision after the decimal point. The value ranges from 0 to 6.

Minimum value: 4713-11-24 BC 00:00:00.000000 (4713 B.C.). Maximum value: 294277-01-09 AD 00:00:00.000000 (294277 A.D.).

After sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1', they will be replaced with the TIMESTAMP[(p)] [WITH TIME ZONE] type. However, the format, range, and precision specifications are different.

  • Input format: YYYY-MM-DD hh:mm:ss.ffffff+timezone with separators and YYYYMMDDhhmmss.ffffff without separators are supported.
    • If separators are used, the plus sign (+) and colon (:) cannot be used as separators between the year, month, and day. (Some separators are not supported when they are used together. The specifications inherit the existing implementation. For example, timestamp '2020-12?12 00:00:00'.) Only colons (:) can be used as separators in the time part. The input format is not affected by the DateStyle parameter.
    • In the scenario where separators are not used and the input is incomplete, the output result may be incorrect though no error is reported.
    • If the year has only two digits, 00–69 corresponds to 2000–2069, and 70–99 corresponds to 1970–1999.
    • If the year contains only one digit, for example, timestamp '1-1-1 00:00:00', the result is '0001-01-01 00:00:00'.
  • Output format: Only YYYY-MM-DD hh:mm:ss.ffffff is supported and the format is not affected by the DateStyle parameter. All zeros at the end of the decimal part of the time are automatically ignored.
  • Range
    • If the value of sql_mode contains strict_trans_tables, the value range is the same as that of TIMESTAMP[(p)] WITH TIME ZONE. If the value is invalid or exceeds the range, an error is reported.
    • If the value of sql_mode does not contain strict_trans_tables, the values of year, month, and day can be 0. However, the values will be converted to valid values in the sequence of year, month, and day. For example, '0000-00-10 00:00:00' will be converted to 0002-12-10 00:00:00 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 00:00:00 is returned.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If p is set to a value greater than 6, the value 6 is used.
    • When it is used as the data type of a table column, the default precision is 0.
    • When it is used as an expression (for example, timestamp '2000-01-01 00:00:00.123456'), the default precision is 6.
  • Time zone: The value ranges from –15:59 to +15:59. You can also use the time zone name (see the system view PG_TIMEZONE_NAMES). The time zone specifications are the same as those of the TIMESTAMP[(p)] WITH TIME ZONE type. Converts the time value of a specified time zone to the time of the UTC-0 time zone. When it is displayed, the time in the UTC-0 time zone is converted to the time in the current time zone of the server. You can run SHOW TIME ZONE to view the time zone of the server. Therefore, when you use the SET TIME ZONE statement to change the time zone of the server, the displayed result also changes.

8 bytes

TIMESTAMP[(p)][WITH TIME ZONE]

Date and time (with time zone). TIMESTAMP is also called TIMESTAMPTZ.

p indicates the precision after the decimal point. The value ranges from 0 to 6.

Minimum value: 4713-11-24 BC 00:00:00.000000 (4713 B.C.). Maximum value: 294277-01-09 AD 00:00:00.000000 (294277 A.D.).

Time zone update: In some countries or regions, the time zone information is frequently updated due to political, economic, and war factors. Therefore, the database system needs to modify the time zone file to ensure that the time is correct.

Currently, the GaussDB time zone type involves only timestamp with timezone. When a new time zone file takes effect, the existing data is not changed, and the new data is adjusted based on the time zone file information.

8 bytes

SMALLDATETIME

Date and time (without a time zone).

The precision level is minute. 30s to 59s are rounded into one minute.

Minimum value: 4713-11-24BC 00:00:00.000000 (4713 B.C.). Maximum value: 294277-01-09AD 00:00:00.000000 (294277 A.D.).

8 bytes

INTERVAL DAY (l) TO SECOND (p)

Specifies the time interval (X days X hours X minutes X seconds).

  • l: indicates the precision of days. The value ranges from 0 to 6. For compatibility, the precision functions are not supported.
  • p: indicates the precision of seconds. The value ranges from 0 to 6. The digit 0 at the end of a decimal number is not displayed.

16 bytes

INTERVAL [FIELDS] [ (p) ]

Time interval.

  • FIELDS: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, or MINUTE TO SECOND.
  • p: indicates the precision of seconds. The value ranges from 0 to 6. p takes effect only when FIELDS is set to SECOND, DAY TO SECOND, HOUR TO SECOND, or MINUTE TO SECOND. The digit 0 at the end of a decimal number is not displayed.

12 bytes

reltime

Relative time interval.

  • The format is as follows: X years X mons X days XX:XX:XX.
  • The Julian calendar is used. It specifies that a year has 365.25 days and a month has 30 days. The relative time interval needs to be calculated based on the input value.

4 bytes

abstime

Date and time.

  • Format: YYYY-MM-DD hh:mm:ss+timezone.
  • The value range is from 1901-12-13 20:45:53 GMT to 2038-01-18 23:59:59 GMT. The precision is second.

4 bytes

datetime[(p)]

Date and time.

This parameter takes effect only when sql_compatibility is set to 'MYSQL', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'. The datetime[(p)] type is replaced by the TIMESTAMP[(p)] WITHOUT TIME ZONE type, but the format, range, precision, and time zone processing specifications are different.

  • Input format: YYYY-MM-DD hh:mm:ss.ffffff+timezone with separators and YYYYMMDDhhmmss.ffffff without separators are supported.
    • If separators are used, the plus sign (+) and colon (:) cannot be used as separators between the year, month, and day. (Some separators are not supported when they are used together. The specifications inherit the existing implementation. For example, datetime '2020-12?12 00:00:00'.) Only colons (:) can be used as separators in the time part. The input format is not affected by the DateStyle parameter.
    • In the scenario where separators are not used and the input is incomplete, the output result may be incorrect though no error is reported.
    • If the year has only two digits, 00–69 corresponds to 2000–2069, and 70–99 corresponds to 1970–1999.
    • If the year contains only one digit, for example, datetime '1-1-1 00:00:00', the result is '0001-01-01 00:00:00'.
  • Output format: Only YYYY-MM-DD hh:mm:ss.ffffff is supported and the format is not affected by the DateStyle parameter. All zeros at the end of the decimal part of the time are automatically ignored.
  • Range
    • If the value of sql_mode contains strict_trans_tables, the value range is the same as that of TIMESTAMP[(p)] WITHOUT TIME ZONE. If the value is invalid or exceeds the range, an error is reported.
    • If the value of sql_mode does not contain strict_trans_tables, the values of year, month, and day can be 0. However, the values will be converted to valid values in the sequence of year, month, and day. For example, '0000-00-10 00:00:00' will be converted to 0002-12-10 00:00:00 BC. If the input is invalid or exceeds the range, a warning message is reported and the value 0000-00-00 00:00:00 is returned.
  • Precision: p indicates the precision after the decimal point. The value ranges from 0 to 6. If the specified precision exceeds 6, the value 6 is used.
    • When it is used as the data type of a table column, the default precision is 0.
    • When it is used as an expression (for example, datetime '2000-01-01 00:00:00.123456'), the default precision is 6.
  • Time zone: The value ranges from –15:59 to +15:59. You can also use the time zone name (see the system view PG_TIMEZONE_NAMES). The time zone specifications are the same as those of the TIMESTAMP[(p)] WITH TIME ZONE type. It converts the time value of a specified time zone to the time of the current time zone of the server. You can run SHOW TIME ZONE to view the time zone of the server. If the converted date and time are saved to the table, the time zone is not converted when the time zone of the server changes.

8 bytes

year[(w)]

Year.

It takes effect only after sql_compatibility is set to 'MYSQL'.

  • Input formats:
    • A string of four digits, ranging from '1901' to '2155' (that is, the years from 1901 to 2155).
    • Four digits, ranging from 1901 to 2155 (that is, the years from 1901 to 2155).
    • A string of one or two digits, ranging from '0' to '99'. The values '0' to '69' indicate the years from 2000 to 2069, and the values '70' to '99' indicate the years from 1970 to 1999. '0' and '00' indicate the year 2000.
    • One or two digits, ranging from 0 to 99. The values 1 to 69 indicate the years from 2001 to 2069, and 70 to 99 indicate the years from 1970 to 1999. The value 0 indicates 0000.
    • Return values of other time functions, for example, now().
  • Output format: Only the YYYY format is supported. w indicates the number of digits in the output format. Only four digits are supported. If not specified, the default value is 4.
  • Value range: 1901 to 2155.
    • If the value of sql_mode contains strict_trans_tables, an error is reported if the value is invalid or exceeds the range.
    • If the value of sql_mode does not contain strict_trans_tables, the invalid value is converted to 0000 and inserted, and an alarm is generated.

1 byte
  1. The data of the time type automatically ignores all zeros at the end of the data when it is displayed.
  2. The default value of p is 6.
  3. For the INTERVAL type, the date and time are stored in the int32 and double types in the system. Therefore, the value ranges of the two types are the same as those of the corresponding data type.
  4. If the insertion time is out of the range, the system may not report an error, but may not ensure that the operation is normal.
If the values of a_format_version and a_format_dev_version are 10c and s1, the default DATE value is determined by the following:
  • Year: returned through SYSDATE
  • Month: returned through SYSDATE
  • Day: 01 (first day of the month)
  • Hour, minute, and second: all 0

Example:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
-- Create a table.
gaussdb=# CREATE TABLE date_type_tab(coll date);

-- Insert data.
gaussdb=# INSERT INTO date_type_tab VALUES (date '12-10-2010');

-- View data.
gaussdb=# SELECT * FROM date_type_tab;
    coll
------------
  2010-12-10
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE date_type_tab;

-- Create a table.
gaussdb=# CREATE TABLE time_type_tab (da time without time zone ,dai time with time zone,dfgh timestamp without time zone,dfga timestamp with time zone, vbg smalldatetime);

-- Insert data.
gaussdb=# INSERT INTO time_type_tab VALUES ('21:21:21','21:21:21 pst','2010-12-12','2013-12-11 pst','2003-04-12 04:05:06');

-- View data.
gaussdb=# SELECT * FROM time_type_tab;
    da    |     dai     |        dfgh         |          dfga          |         vbg         
----------+-------------+---------------------+------------------------+---------------------
 21:21:21 | 21:21:21-08 | 2010-12-12 00:00:00 | 2013-12-11 16:00:00+08 | 2003-04-12 04:05:00
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE time_type_tab;

-- Create a table.
gaussdb=# CREATE TABLE day_type_tab (a int,b INTERVAL DAY(3) TO SECOND (4)); 

-- Insert data.
gaussdb=# INSERT INTO day_type_tab VALUES (1, INTERVAL '3' DAY);

-- View data.
gaussdb=# SELECT * FROM day_type_tab;
 a |   b    
---+--------
 1 | 3 days
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE day_type_tab;

-- Create a table.
gaussdb=# CREATE TABLE year_type_tab(a int, b interval year (6));

-- Insert data.
gaussdb=# INSERT INTO year_type_tab VALUES(1,interval '2' year);

-- View data.
gaussdb=# SELECT * FROM year_type_tab;
 a |    b    
---+---------
 1 | 2 years
(1 row)

gaussdb=# SELECT TIME 'allballs';
   time
----------
 00:00:00
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE year_type_tab;

-- Example of the datetime and timestamp data types in MySQL-compatible mode.
-- Create a database in MySQL-compatible mode.
-- Switch to the database in MySQL-compatible mode.
gaussdb=# CREATE DATABASE gaussdb_m dbcompatibility='MYSQL';
gaussdb=# \c gaussdb_m;

-- Set compatible version control parameters.
gaussdb_m=# SET b_format_version = '5.7';
gaussdb_m=# SET b_format_dev_version = 's1';

-- Create a table.
gaussdb_m=# CREATE TABLE datetime_typ_tab(col1 datetime, col2 timestamp);

-- Insert data.
gaussdb_m=# INSERT INTO datetime_typ_tab VALUES ('2003-04-12 04:05:06+09:00', '2003-04-12 04:05:06+09:00');

-- View data.
gaussdb_m=# SELECT * FROM datetime_typ_tab;
        col1         |        col2         
---------------------+---------------------
 2003-04-12 03:05:06 | 2003-04-12 03:05:06
(1 row)

-- Drop the table.
gaussdb_m=# DROP TABLE datetime_typ_tab;

-- Create a table.
gaussdb_m=# CREATE TABLE year_typ_tab(col1 year, col2 year(4));

-- Insert data.
gaussdb_m=# INSERT INTO year_typ_tab VALUES ('2023', now());

-- View the data.
gaussdb_m=# SELECT * FROM year_typ_tab;
 col1 | col2 
------+------
 2023 | 2023
(1 row)

-- Delete the table and database.
gaussdb_m=# DROP TABLE year_typ_tab;
gaussdb_m=# \c postgres;
gaussdb=# DROP DATABASE gaussdb_m;

-- Reset parameters.
gaussdb=# RESET ALL;

Date Inputs

Date and time input is accepted in almost any reasonable formats, including ISO 8601 and SQL-compatible. The system allows you to customize the sequence of day, month, and year in the date input. Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to select day-month-year interpretation, or YMD to select year-month-day interpretation.

Remember that any date or time literal input needs to be enclosed with single quotation marks (''), and the syntax is as follows:

type [ ( p ) ] 'value'

The p that can be selected in the precision statement is an integer, indicating the number of fractional digits in the seconds column. Table 2 shows the input formats of the date type.

Table 2 Date input formats

Example

Description

1999-01-08

ISO 8601 (recommended format). January 8, 1999 in any format.

January 8, 1999

Unambiguous in any datestyle input mode

1/8/1999

January 8 in MDY format. August 1 in DMY format.

1/18/1999

January 18 in MDY format, rejected in other formats.

01/02/03
  • January 2, 2003 in MDY format.
  • February 1, 2003 in DMY format.
  • February 3, 2001 in YMD format.

1999-Jan-08

January 8 in any mode

Jan-08-1999

January 8 in any mode

08-Jan-1999

January 8 in any mode

99-Jan-08

January 8 in YMD mode, else error

08-Jan-99

January 8, except error in YMD format.

Jan-08-99

January 8, except error in YMD format.

19990108

ISO 8601. January 8, 1999 in any format.

990108

ISO 8601. January 8, 1999 in any format.

1999.008

Year and day of year.

J2451187

Julian date.

January 8, 99 BC

Year 99 B.C.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
-- Create a table.
gaussdb=# CREATE TABLE date_type_tab(coll date);

-- Insert data.
gaussdb=# INSERT INTO date_type_tab VALUES (date '12-10-2010');

-- View data.
gaussdb=# SELECT * FROM date_type_tab;
        coll         
---------------------
 2010-12-10
(1 row)

-- View the date format.
gaussdb=# SHOW datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

-- Set the date format.
gaussdb=# SET datestyle='YMD';
SET

-- Insert data.
gaussdb=# INSERT INTO date_type_tab VALUES(date '2010-12-11');

-- View data.
gaussdb=# SELECT * FROM date_type_tab;
        coll         
---------------------
 2010-12-10
 2010-12-11
(2 rows)

-- Drop the table.
gaussdb=# DROP TABLE date_type_tab;

Time

The time-of-day types are TIME [(p)] [WITHOUT TIME ZONE] and TIME [(p)] [WITH TIME ZONE]. TIME alone is equivalent to TIME WITHOUT TIME ZONE.

If a time zone is specified in the input for TIME WITHOUT TIME ZONE, it is silently ignored.

For details about the time input types, see Table 3. For details about time zone input types, see Table 4.

Table 3 Time input types

Example

Description

05:06.8

ISO 8601

4:05:06

ISO 8601

4:05

ISO 8601

040506

ISO 8601

4:05 AM

Same as 04:05. Input hours must be less than or equal to 12.

4:05 PM

Same as 16:05. Input hours must be less than or equal to 12.

04:05:06.789-8

ISO 8601

04:05:06-08:00

ISO 8601

04:05-08:00

ISO 8601

040506-08

ISO 8601

04:05:06 PST

Time zone specified by abbreviation

2003-04-12 04:05:06 America/New_York

Time zone specified by full name
Table 4 Time zone input types

Example

Description

PST

Abbreviation (for Pacific Standard Time)

America/New_York

Full time zone name

-8:00

ISO-8601 offset for PST

-800

ISO-8601 offset for PST

-8

ISO-8601 offset for PST

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
gaussdb=# SELECT time '04:05:06';
   time   
----------
 04:05:06
(1 row)

gaussdb=# SELECT time '04:05:06 PST';
   time   
----------
 04:05:06
(1 row)

gaussdb=# SELECT time with time zone '04:05:06 PST';
   timetz    
-------------
 04:05:06-08
(1 row)

Special Values

The special values supported by GaussDB are converted to common date/time values when being read. For details, see Table 5.

Table 5 Special values

Input String

Applicable Type

Description

epoch

date and timestamp

1970-01-01 00:00:00+00 (Unix system time zero)

infinity

timestamp

Later than any other timestamps

-infinity

timestamp

Earlier than any other timestamps

now

date, time, and timestamp

Start time of the current transaction

today

date and timestamp

Midnight today

tomorrow

date and timestamp

Midnight tomorrow

yesterday

date and timestamp

Midnight yesterday

allballs

time

00:00:00.00 UTC

Example:

-- Create a table.
gaussdb=# CREATE TABLE realtime_type_special(col1 varchar(20), col2 date, col3 timestamp, col4 time);

-- Insert data.
gaussdb=# INSERT INTO realtime_type_special VALUES('epoch', 'epoch', 'epoch', NULL);
gaussdb=# INSERT INTO realtime_type_special VALUES('now', 'now', 'now', 'now');
gaussdb=# INSERT INTO realtime_type_special VALUES('today', 'today', 'today', NULL);
gaussdb=# INSERT INTO realtime_type_special VALUES('tomorrow', 'tomorrow', 'tomorrow', NULL);
gaussdb=# INSERT INTO realtime_type_special VALUES('yesterday', 'yesterday', 'yesterday', NULL);

-- View data.
gaussdb=# SELECT * FROM realtime_type_special;
   col1    |        col2         |            col3            |      col4
-----------+---------------------+----------------------------+-----------------
 epoch     | 1970-01-01 00:00:00 | 1970-01-01 00:00:00        |
 now       | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
 today     | 2023-02-27 00:00:00 | 2023-02-27 00:00:00        |
 tomorrow  | 2023-02-28 00:00:00 | 2023-02-28 00:00:00        |
 yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00        |
(5 rows)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 < 'infinity';
   col1    |        col2         |            col3            |      col4
-----------+---------------------+----------------------------+-----------------
 epoch     | 1970-01-01 00:00:00 | 1970-01-01 00:00:00        |
 now       | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
 today     | 2023-02-27 00:00:00 | 2023-02-27 00:00:00        |
 tomorrow  | 2023-02-28 00:00:00 | 2023-02-28 00:00:00        |
 yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00        |
(5 rows)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 > '-infinity';
   col1    |        col2         |            col3            |      col4
-----------+---------------------+----------------------------+-----------------
 epoch     | 1970-01-01 00:00:00 | 1970-01-01 00:00:00        |
 now       | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
 today     | 2023-02-27 00:00:00 | 2023-02-27 00:00:00        |
 tomorrow  | 2023-02-28 00:00:00 | 2023-02-28 00:00:00        |
 yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00        |
(5 rows)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 > 'now';
   col1   |        col2         |        col3         | col4
----------+---------------------+---------------------+------
 tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
(1 row)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 = 'today';
 col1  |        col2         |        col3         | col4
-------+---------------------+---------------------+------
 today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
(1 row)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 = 'tomorrow';
   col1   |        col2         |        col3         | col4
----------+---------------------+---------------------+------
 tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
(1 row)

gaussdb=# SELECT * FROM realtime_type_special WHERE col3 > 'yesterday';
   col1   |        col2         |            col3            |      col4
----------+---------------------+----------------------------+-----------------
 now      | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
 today    | 2023-02-27 00:00:00 | 2023-02-27 00:00:00        |
 tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00        |
(3 rows)

-- Drop the table.
gaussdb=# DROP TABLE realtime_type_special;

Interval Input

The input of reltime can be any valid interval in text format. It can be a number (negative numbers and decimals are also allowed) or a specific time, which must be in SQL standard format or ISO-8601 format. In addition, the text input needs to be enclosed with single quotation marks ('').

For details about interval input, see Table 6.

Table 6 Interval input types

Input

Output

Description

60

2 mons

Numbers are used to indicate intervals. The default unit is day. Decimals and negative numbers are allowed. Particularly, a negative interval syntactically means how long before.

31.25

1 mons 1 days 06:00:00

-365

-12 mons -5 days

1 years 1 mons 8 days 12:00:00

1 years 1 mons 8 days 12:00:00

Intervals are in POSTGRES format. They can contain both positive and negative numbers and are case-insensitive. Output is a simplified POSTGRES interval converted from the input.

-13 months -10 hours

-1 years -25 days -04:00:00

-2 YEARS +5 MONTHS 10 DAYS

-1 years -6 mons -25 days -06:00:00

P-1.1Y10M

-3 mons -5 days -06:00:00

Intervals are in ISO-8601 format. They can contain both positive and negative numbers and are case-insensitive. Output is a simplified POSTGRES interval converted from the input.

-12H

-12:00:00

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
-- Create a table.
gaussdb=# CREATE TABLE reltime_type_tab(col1 character(30), col2 reltime);

-- Insert data.
gaussdb=# INSERT INTO reltime_type_tab VALUES ('90', '90');
gaussdb=# INSERT INTO reltime_type_tab VALUES ('-366', '-366');
gaussdb=# INSERT INTO reltime_type_tab VALUES ('1975.25', '1975.25');
gaussdb=# INSERT INTO reltime_type_tab VALUES ('-2 YEARS +5 MONTHS 10 DAYS', '-2 YEARS +5 MONTHS 10 DAYS');
gaussdb=# INSERT INTO reltime_type_tab VALUES ('30 DAYS 12:00:00', '30 DAYS 12:00:00');
gaussdb=# INSERT INTO reltime_type_tab VALUES ('P-1.1Y10M', 'P-1.1Y10M');

-- View data.
gaussdb=# SELECT * FROM reltime_type_tab;
              col1              |                col2                 
--------------------------------+-------------------------------------
 90                             | 3 mons
 -366                           | -1 years -18:00:00
 1975.25                        | 5 years 4 mons 29 days
 -2 YEARS +5 MONTHS 10 DAYS     | -1 years -6 mons -25 days -06:00:00
 30 DAYS 12:00:00               | 1 mon 12:00:00
 P-1.1Y10M                      | -3 mons -5 days -06:00:00
(6 rows)

-- Drop the table.
gaussdb=# DROP TABLE reltime_type_tab;
Parent topic: Data Types