PostgreSQL Date/Time Data Types

Please refer the post PostgreSQL Data Types if you want to see the complete list

Like any other database PostgreSQL also supports extensive Date/Time features. But in general date and time data types are the most annoying and confusing data type due to the extensive formats like day, month, year, timestamp, timezone etc.

Why Date/Time formatting is so complicated ?

There are many reasons

  1. Different date format in different countries – Different countries adopted different Date format ( for more details read in Wikipedia Link )
  2. Different system time like Unix Date/Time, Database Date/Time and each system may follow different format
  3. Different Programming Language/Tool default Date/Time format, For example Java time() and Python Time() are different
  4. Timezone and Offsets – In short different country have their own timezones and offsets, this create lot of ruckus in date/time conversion etc.

Date, time, timestamp and interval are the main sub types. Except interval all of these can be used with or without timezone

Name Storage Size Description
timestamp[(p)] without timezone 8 bytes both date and time (no time zone)
timestamp[(p)] with timezone 8 bytes both date and time (with time zone)
date 4 bytes date (no time of the day )
time[(p)] without timezone 8 bytes time of day (no date)
time[(p)] with timezone 12 bytes time of day (no date), with time zone
interval[ fields ][(p)] 8 bytes time interval

Timestamp (without time zone)

 “TIMESTAMP” data type creates without time zone

postgres=# create table test(ts TIMESTAMP);
CREATE TABLE
postgres=# insert into test values(now());
INSERT 0 1
postgres=# select ts from test;
             ts             
----------------------------
 2022-07-04 17:34:14.907618
(1 row)

ts return timestamp without time zone. timezone(6) defines the millisecond precision upto 6 decimal. By default timestamp assumes 6 decimal precision.

timezone(6) –> 2022-07-04 17:34:14.907618

timezone(5) –> 2022-07-04 17:34:14.90761

timezone(4) –> 2022-07-04 17:34:14.9076

timezone(3) –> 2022-07-04 17:34:14.907

timezone(2) –> 2022-07-04 17:34:14.90

timezone(1) –> 2022-07-04 17:34:14.9

timezone –> 2022-07-04 17:34:14.907618 — (default 6)

Timestamp (with time zone)

 “TIMESTAMPTZ” data type creates with time zone

postgres=# create table test(ts TIMESTAMPTZ);
CREATE TABLE
postgres=# insert into test values(now());
INSERT 0 1
postgres=# select ts from test;
             ts             
----------------------------
 2022-07-04 17:42:18.656129+05:30
(1 row)

ts return timestamp with time zone. Note that +05:30 denote the timezone

Date

 “Date” defines a date without time of the day

postgres=# create table test(ts DATE);
CREATE TABLE
postgres=# insert into test values(now());
INSERT 0 1
postgres=# select ts from test;
             ts             
----------------------------
 2022-07-04
(1 row)

Time (without timezone)

“time” data type declared time without time zone

postgres=# create table test(ts TIME);
CREATE TABLE
postgres=# insert into test values(current_time);
INSERT 0 1
postgres=# insert into test values(CLOCK_TIMESTAMP());
INSERT 0 1
postgres=# select ts from test;
             ts             
 -----------------
 17:55:07.266251
 17:56:13.870309
(1 row)

Time (with timezone)

timetz” data type declared time with time zone

postgres=# create table test(ts TIMETZ);
CREATE TABLE
postgres=# insert into test values(TIME '2003-04-12 04:05:06 America/New_York');
INSERT 0 1
postgres=# select ts from test;
       ts       
----------------
 04:05:06+05:30
(1 row)

04:05:06 PST –> time zone specified by abbreviation

04:05:06 America/New_York –> time zone specified by full name

Interval

Interval used to calculate or extract different date/time field

Example :- SELECT EXTRACT(hours from ’80 minutes’::interval);

Interval Unit Abbreviations

AbbreviationMeaning
YYears
MMonths
WWeeks
DDays
HHour
MMinutes
SSeconds

Some formatting functions

Operator and DescriptionExample
date + integer → datedate ‘2001-09-28’ + 7 → 2001-10-05
date + interval → timestampdate ‘2001-09-28’ + interval ‘1 hour’ → 2001-09-28 01:00:00
date + time → timestampdate ‘2001-09-28′ + time ’03:00’ → 2001-09-28 03:00:00
interval + interval → intervalinterval ‘1 day’ + interval ‘1 hour’ → 1 day 01:00:00
timestamp + interval → timestamptimestamp ‘2001-09-28 01:00′ + interval ’23 hours’ →
2001-09-29 00:00:00
time + interval → timetime ’01:00′ + interval ‘3 hours’ → 04:00:00
interval → interval
interval to intervalinterval ’23 hours’ → -23:00:00
date – date → integerdate ‘2001-10-01’ – date ‘2001-09-28’ → 3
date – integer → datedate ‘2001-10-01’ – 7 → 2001-09-24
date – interval → timestampdate ‘2001-09-28’ – interval ‘1 hour’ → 2001-09-27 23:00:00
time – time → intervaltime ’05:00′ – time ’03:00′ → 02:00:00
time – interval → timetime ’05:00′ – interval ‘2 hours’ → 03:00:00
timestamp – interval → timestamptimestamp ‘2001-09-28 23:00′ – interval ’23 hours’ →
2001-09-28 00:00:00
interval – interval → intervalinterval ‘1 day’ – interval ‘1 hour’ → 1 day -01:00:00
timestamp – timestamp → intervaltimestamp ‘2001-09-29 03:00’ – timestamp ‘2001-07-27 12:00’ → 63 days 15:00:00
interval * double precision → intervalinterval ‘1 second’ * 900 → 00:15:00
interval ‘1 day’ * 21 → 21 days
interval ‘1 hour’ * 3.5 → 03:30:00
interval / double precision → intervalinterval ‘1 hour’ / 1.5 → 00:40:00

In the next blog we will cover date time functions and operators, additional functions like age, overlap, date_part() etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: