PostgreSQL | Data Types | Date and Time Types

Date and time types

Type	Size	Description	Alias
`timestamp [(p)] without time zone`	8 bytes	Date and time
`timestamp [(p)] with time zone`	8 bytes	Date, time, and time zone	`timestamptz`
`interval [(p)]`	12 bytes	Time interval
`date`	4 bytes	Date
`time [(p)] without time zone`	8 bytes	Time
`time [(p)] with time zone`	12 bytes	Time and time zone	`timetz`

The optional precision p specifies fractional-second precision from 0 through 6. PostgreSQL accepts many date formats; ambiguous formats depend on DateStyle.

January 8, 1999     1999-01-08       1999-Jan-08
Jan-08-1999         08-Jan-1999      19990108
990108               1999.008

04:05:06.789        04:05:06         04:05
040506               04:05 AM         04:05 PM

04:05:06 PST        04:05:06+09:00   04:05:06+0900

Korean Standard Time is +09:00. Month names may use full or abbreviated English names, such as January/Jan and September/Sep/Sept. Enclose date and time literals in single quotes:

timestamp                     '2004-10-19 10:23:54'
timestamp with time zone      '2004-10-19 10:23:54+09'
date                          '2004-10-19'
time                          '10:23:54'
time with time zone           '10:23:54+09'

Use interval for date/time arithmetic:

select now(), now() - interval '30 minute';
select now(), now()::date - '1 day'::interval;

Other operations

Current date, time, and time zone

select now();
show timezone;
set time zone 'Asia/Seoul';
select current_date, current_time, timeofday();
select now(), current_timestamp, timestamp 'now';

Extract and truncate date/time fields

date_part and extract return a field; date_trunc rounds down to the specified unit.

-- Year
select date_part('year', timestamp '2020-07-30 20:38:40');
select date_part('year', current_timestamp);
select extract('isoyear' from date '2006-01-01'); -- 2005
select extract('isoyear' from current_timestamp);
select date_trunc('year', timestamp '2020-07-30 20:38:40');
select date_trunc('year', current_timestamp);

-- Month
select date_part('month', timestamp '2020-07-30 20:38:40');
select extract('month' from interval '2 years 3 months');
select extract('month' from interval '2 years 13 months'); -- 1
select date_trunc('month', timestamp '2020-07-30 20:38:40');

-- Day, hour, minute, and second
select date_part('day', timestamp '2020-07-30 20:38:40');
select date_trunc('day', timestamp '2020-07-30 20:38:40');
select date_part('hour', interval '4 hours 3 minutes');
select date_trunc('hour', timestamp '2020-07-30 20:38:40');
select date_part('minute', timestamp '2020-07-30 20:38:40');
select date_trunc('minute', timestamp '2020-07-30 20:38:40');
select extract('second' from time '17:12:28.5');
select date_trunc('second', timestamp '2013-07-30 20:38:40');

-- Century and day numbering
select extract('century' from timestamp '2000-12-16 12:21:13'); -- 20
select extract('century' from timestamp '2020-07-30 20:38:40'); -- 21
select date_trunc('century', timestamp '2020-07-30 20:38:40');
select extract('isodow' from timestamp '2013-07-30 20:38:40');
select extract('doy' from timestamp '2013-07-30 20:38:40'); -- 211
select extract('doy' from timestamp '2020-07-30 20:38:40'); -- 212