Time and Date Type

Remarks

The time and date are accurate to milliseconds.

**Table 1** Time and Date Type
Name	Description	Storage Space
DATE	Date and time. Only the ISO 8601 format is supported, for example 2020-01-01.	32 bits
TIME	Time (hour, minute, second, millisecond) without a time zone Example: TIME '01:02:03.456'	64 bits
TIME WITH TIMEZONE	Time with a time zone (hour, minute, second, millisecond). Time zones are expressed as the numeric UTC offset value. Example: TIME '01:02:03.456 -08:00'	96 bits
TIMESTAMP	Timestamp	64 bits
TIMESTAMP WITH TIMEZONE	Timestamp with time zone	64 bits
INTERVAL YEAR TO MONTH	Time interval literal year and month, in the format of SY-M. S: optional symbols (+/-) Y: years M: months	128 characters
INTERVAL DAY TO SECOND	The time interval literally indicates the day, hour, minute, and second, and is accurate to millisecond. The format is SD H:M:S.nnn. S: optional symbols (+/-) D: days M: minutes S: seconds nnn: milliseconds	128 characters

Example:

-- Query the date:
SELECT DATE '2020-07-08';
   _col0    
------------
 2020-07-08 
(1 row)

-- Query time:
SELECT TIME '23:10:15';
 _col0     
--------------
 23:10:15 
(1 row)

SELECT TIME '01:02:03.456 -08:00';
 _col0     
--------------
 01:02:03.456-08:00 
(1 row)

-- Time interval usage
SELECT TIMESTAMP '2015-10-18 23:00:15' + INTERVAL '3 12:15:4.111' DAY TO SECOND;      
 _col0          
------------------------- 
 2015-10-22 11:15:19.111 
(1 row)

SELECT TIMESTAMP '2015-10-18 23:00:15' + INTERVAL '3-1' YEAR TO MONTH;
          _col0          
-------------------------
 2018-11-18 23:00:15 
(1 row)

select INTERVAL '3' YEAR + INTERVAL '2' MONTH ;
 _col0 
-------
 3-2   
(1 row)

select INTERVAL '1' DAY+INTERVAL '2' HOUR +INTERVAL '3' MINUTE +INTERVAL '4' SECOND ;
     _col0      
----------------
 1 02:03:04.000 
(1 row)

Parent topic: Data Type

Previous topic: Character

Next topic: Complex Type