Date time

PostgreSQL Data Type Date time
Type Size Min value Max value Descripiton
timestamp without time zone 8 bytes 4713 BC - without timezone
timestamp with time zone 8 bytes 4713 BC - with timezone
date 4 bytes 4713 BC - date(without time))
time without time zone 8 bytes 00:00:00 - time(without timezone)
time with time zone 12 bytes 00:00:00_1459 - time(with timezone)
interval 16 bytes -178000000 years - Time interval

interval

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • YEAR TO MONTH
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE TO SECOND

Special Keyword

Special Keyword Available Type Description
epoch date, timestamp 1970-01-01 00:00:00+00 (Unix system time zero)
infinity date, timestamp Max time
-infinity date, timestamp Min time
now date, time, timestamp Current time
today date, timestamp Today mid-night 00:00
tomorrow date, timestamp Tomorrow mid-night 00:00
yesterday date, timestamp Yesterday mid-night 00:00
allballs time 00:00:00.00 UTC

Output Format

Format Dexcription Example
ISO ISO 8601, SQL standard 1997-12-17 07:37:16-08
SQL traditional style 12/17/1997 07:37:16.00 PST
Postgres original style Wed Dec 17 07:37:16 1997 PST
German regional style 17.12.1997 07:37:16.00 PST

Reference