Date and time types

The date and time data types are commonly used to describe the occurrence of events, such as birth dates. PostgreSQL supports the following date and time types:

PostgreSQL stores the timestamp with and without the time zone in the Coordinated Universal Time (UTC) format, and only the time is stored without the time zone. This explains the identical storage size for both the timestamp with the time zone and the timestamp without the time zone.

There are two approaches for handling the timestamp correctly. The first approach is to use the timestamp without the time zone, and let the client side handle the time zone differences. This is useful for in-house development, applications with only one-time zone, and when the clients know the time zone differences.

The other approach is to use the timestamp with the time zone. In PostgreSQL, this is given the timestamptz extension. The following are some of the best practices to avoid the common pitfalls when using timestamptz:

• Make sure to set the default time zone for all connections. This is done by setting the time zone configuration in the postgresql.conf file. Since PostgreSQL stores the timestamp with the time zone in UTC format internally, it's a good practice to set the default connection to UTC as well. Also, UTC helps us to overcome the potential problems due to Daylight Savings Time (DST).
• The time zone should be specified in each CRUD operation.
• Don't perform operations on the timestamp without time zone and the timestamp with time zone, this will normally lead to the wrong results due to implicit conversion.
• Don't invent your own conversion; instead, use the database server to convert between the different time zones.
• Investigate the data types of high-level languages to determine which type could be used with PostgreSQL without extra handling.

PostgreSQL has two important settings: timezone and DATESTYLE. DATESTYLE has two purposes:

• Setting the display format: DATESTYLE specifies the timestamp and timestamptz rendering style
• Interpreting ambiguous data: DATESTYLE specifies how to interpret timestamp and timestamptz

The pg_timezone_names and pg_timezone_abbrevs views provide a list of the time zone names and abbreviations, respectively. They also provide information regarding the time offset from UTC, and whether the time zone observes DST. For example, the following code snippet sets the timezone setting to Jerusalem, and then retrieves the local date and time in Jerusalem:

postgres=# SET timezone TO 'Asia/jerusalem';
SET
postgres=# SELECT now();
              now 
-------------------------------
 2018-08-19 19:33:06.806009+03
(1 row)

The PostgreSQL AT TIME ZONE statement converts the timestamp with or without the timezone to a specified time zone; its behavior depends on the converted type. The following example clarifies this construct:

postgres=# SHOW timezone;
    TimeZone 
----------------
 Asia/Jerusalem
(1 row)

postgres=# \x 
Expanded display is on.
postgres=#SELECT
now() AS "Return current timestap in Jerusalem",
now()::timestamp AS "Return current timestap in Jerusalem with out time zone information", 
now() AT TIME ZONE 'CST' AS "Return current time in Central Standard Time without time zone information", 
'2018-08-19:00:00:00'::timestamp AT TIME ZONE 'CST' AS "Convert the time in CST to Jerusalem time zone";

[ RECORD 1 ]---------------------------------------------------------------+------------------------------
Return current timestamp in Jerusalem                                      | 2018-08-19 20:19:20.126501+03
Return current timestamp in Jerusalem with out time zone information       | 2018-08-19 20:19:20.126501
Return current time in Central Standard Time without time zone information | 2018-08-19 11:19:20.126501
Convert the time in CST to Jerusalem time zone                             | 2018-08-19 09:00:00+03

The now() function returns the current timestamp with the time zone in the Asia/Jerusalem time zone. Notice that the time zone offset is +03 because it's summer in Jerusalem. The time zone offset can change due to DST. In winter, the time zone offset in Jerusalem is two hours. 

When casting the timestamp with the time zone to timestamp as in now()::timestamp, the time zone offset is truncated. This can be useful for developers who are developing applications in-house in one site. 

The now() AT TIME ZONE 'CST' expression converts the timestamp with the Jerusalem time zone to the timestamp in the specified time zone, CST. Since the central standard time offset is -6 from UTC, nine hours are deducted, since Jerusalem is offset three hours from UTC.

The last expression, '2018-08-19:00:00:00'::timestamp AT TIME ZONE 'CST', is reinterpreted as a timestamp as being in that time zone, CST, for the purposes of converting it to the connection default time zone, Asia/jerusalem.

You can summarize the conversion between the timestamp with and without the time zone as follows:

• The 'y'::TIMESTAMP WITHOUT TIMEZONE AT TIME ZONE 'x' expression is interpreted as follows: the y value of the TIMESTAMP type will be converted from the x time zone to the session time zone.
• The 'y'::TIMESTAMP WITH TIMEZONE AT TIME ZONE 'x' expression converts the 'y' value of the timestamptz time to a value of the TIMESTAMP type at the specified time zone, x.

PostgreSQL is intelligent in handling timestamps with time zones. The following example shows how PostgreSQL handles DST:

postgres=# SET timezone TO 'Europe/Berlin';
SET
postgres=# SELECT '2017-03-26 2:00:00'::timestamptz;
      timestamptz 
------------------------
 2017-03-26 03:00:00+02
(1 row)

The date is recommended when there is no need to specify the time, such as birthdays, holidays, and absence days. Time with time-zone storage is 12 bytes: 8 bytes are used to store the time, and 4 bytes are used to store the time zone. The time without a time zone consumes only 8 bytes. Conversions between time zones can be made using the AT TIME ZONE construct.

Finally, the interval data type is very important in handling timestamp operations, as well as describing some business cases. From the point of view of functional requirements, the interval data type can represent a period of time, such as estimation time for the completion of a certain task.

The result type of the basic arithmetic operations such as + and - on timestamp, timestamptz, time, and time with time zone is of the interval type. The result of the same operations on the date type is an integer. The following example shows timestamptz and date subtraction. Notice the format of the specifying intervals:

postgres=#SELECT'2014-10-11'::date -'2014-10-10'::date = 1 AS "date Subtraction",
'2014-09-01 23:30:00'::timestamptz -'2014-09-01 22:00:00'::timestamptz= Interval '1 hour, 30 minutes' AS "Time stamp subtraction";

 date Subtraction | Time stamp subtraction 
------------------+------------------------
 t                | t
(1 row)