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	Only for the time within one day.	64 bits
TIME WITH TIMEZONE	Only for the time within one day (including the timezone).	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:15:10.000 
(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.000 
(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