Date/Time

Table 1 lists the date/time types supported by openGauss. For the operators and built-in functions of the types, see Date and Time Processing Functions and Operators.

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

Table 1 Date/Time types

Examples

-- Create a table.
postgres=# CREATE TABLE date_type_tab(coll date);

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

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

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

-- Create a table.
postgres=# 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.
postgres=# 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.
postgres=# 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.
postgres=# DROP TABLE time_type_tab;

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

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

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

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

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

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

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

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

Date Input

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 quotes, 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 some possible inputs for the date type.

Table 2 Date input

Examples

-- Create a table.
postgres=# CREATE TABLE date_type_tab(coll date);

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

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

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

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

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

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

-- Drop the table.
postgres=# 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

Table 4 Time zone input types

Examples

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

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

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

Special Values

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

Table 5 Special values

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, see Table6 Interval input types.

Table 6 Interval input types

Examples

-- Create a table.
postgres=# CREATE TABLE reltime_type_tab(col1 character(30), col2 reltime);

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

-- View data.
postgres=# SELECT * FROM reltime_type_tab;
              col1              |                col2                 
--------------------------------+-------------------------------------
 1975.25                        | 5 years 4 mons 29 days
 -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
 -366                           | -1 years -18:00:00
 90                             | 3 mons
 30 DAYS 12:00:00               | 1 mon 12:00:00
(6 rows)

-- Drop the table.
postgres=# DROP TABLE reltime_type_tab;