Updated on 2024-05-07 GMT+08:00

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.

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

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

4 bytes (8 bytes in A compatibility schema)

TIME [(p)] [WITHOUT TIME ZONE]

Time within one day.

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.

8 bytes

TIME [(p)] [WITH TIME ZONE]

Time within one day (with time zone).

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

  • Minimum value: 00:00:00+1559.
  • Maximum value: 24:00:00.

12 bytes

TIMESTAMP[(p)] [WITHOUT TIME ZONE]

Date and time.

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

  • 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

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-24BC 00:00:00.000000 (4713 B.C.).
  • Maximum value: 294277-01-09AD 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 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

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

-- Delete a 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)

-- Delete a 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)

-- Delete a 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)

-- Delete a table.
gaussdb=# DROP TABLE year_type_tab;

Date Inputs

Date and time input is accepted in almost any reasonable formats, including ISO 8601, SQL-compatible, and traditional Postgres. 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 mode
  • February 1, 2003 in DMY mode
  • February 3, 2001 in YMD mode

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 mode

Jan-08-99

January 8, except error in YMD mode

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 BC

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

-- Delete a 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 4. For details about time zone input types, see Table 3.

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)

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

-- Delete a 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, ISO-8601 format, or POSTGRES 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)

-- Delete a table.
gaussdb=# DROP TABLE reltime_type_tab;