Backward compatibility

To ensure compatibility with earlier versions of PostgreSQL, the developers have continued to provide the older datetime and timespan data types. The datetime type is now equivalent to timestamp, while the timespan is now equivalent to the interval types.

Other date/time data types include abstime and reltime, which are lower precision types. However, these types are internal to PostgreSQL, and any or all of these types may disappear in a future release. It is advised therefore to design new applications with the SQL-compliant data types in mind, and to convert older applications from any of these data types as soon as is possible.

Date conventions

Date input can be accepted by PostgreSQL in many common formats, including the ISO-8601 format, the traditional SQL format, the original PostgreSQL format, and more. Table 3-15 lists several of these date formats.

These formats are relevant to the date and the timestamp data types.

When specifying a named month in a date value to PostgreSQL, you may either type the complete month name, or choose from a set of defined abbreviations for each month. These abbreviations are listed in Table 3-16.

Similarly, Table 3-17 lists PostgreSQL-recognized abbreviations for weekday names.

Despite the wide variety of ways in which PostgreSQL can interpret date values, the values are always stored uniformally, and will be returned in a consistent format. As such, you have a variety of methods available to you to customize the default behavior with which date and time values are returned to you.

To set the general date/time output format, the SET command can be applied to the run-time variable DATESTYLE. This variable may be set to one of four available general styles shown in Table 3-18.

As an example, you can use the following SQL statement to set the date style to SQL :

booktown=# 
SET DATESTYLE TO SQL;

SET VARIABLE

If you perform a SELECT current_timestamp query after setting this variable, PostgreSQL should return the current time using the ISO format as instructed:

booktown=# 
SELECT current_timestamp;

         timestamp
----------------------------
 08/10/2001 13:25:55.00 PDT
(1 row)

The SHOW command can be used to display the current value of the DATESTYLE variable while PostgreSQL is running.

booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is SQL with US (NonEuropean) conventions
SHOW VARIABLE

In addition to these general formats, PostgreSQL's date output format has two other variants which further describe how to display the date, shown in Table 3-19: European and non-European (U.S.). These determine whether the format is day followed by month, or vice versa. This variation can be applied on top of the previous four general formats with the same syntax to SET DATESTYLE and will not modify your chosen format except for the arrangement of the month and day.

Furthermore, you may set both the general format and day/month convention by supplying both variables to the SET command, comma delimited. The order of these variables is not important to the SET command as long as the variables are not mutually exclusive (e.g., SQL and ISO ), as shown in Example 3-26.

booktown=# 
SET DATESTYLE TO ISO,US;

SET VARIABLE
booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
SHOW VARIABLE
booktown=# 
SET DATESTYLE TO NONEUROPEAN, GERMAN;

SET VARIABLE
booktown=# 
SHOW DATESTYLE;

NOTICE:  DateStyle is German with European conventions
SHOW VARIABLE

If you do not specify a month/day format, a reasonable default will usually be chosen (e.g., European is the default for the German regional format).

While SET DATESTYLE is a convenient way to set the output format, it is important to note that this is a run-time variable , which means that it exists only for the lifespan of your connected session. There are two methods available that allow you to provide a default value for the DATESTYLE variable, which lets you avoid explicitly setting the variable for each new session you begin:

• You may change the PGDATESTYLE environment variable on the server running postmaster . For example, with the bash shell, you could add the export PGDATESTYLE="SQL US" line to the postgres user's .bash_ profile file. When the postgres user starts postmaster , the PGDATESTYLE variable will be read and applied globally to all date and time formatting performed by PostgreSQL.

• You may change the PGDATESTYLE environment variable used by a client application (assuming it was written with the libpq library) on its session start-up, if you wish the client rather than the server to configure the output. For example, setting the PGDATESTYLE variable at a bash prompt with the export command before starting psql sets the format for psql to use.

Time conventions

Time values, like date values, may be entered in to a table in a number of ways. Commonly used formats are listed in Table 3-20. These apply to values of type time and time with time zone.

In addition to these formats, PostgreSQL allows for further description of a time value which is defined as time with time zone by supporting extra time zone parameters following the time value. The supported formats are illustrated in Table 3-21.

The time with time zone data type is mainly supported by PostgreSQL to adhere to existing SQL standards and for portability with other database management systems. If you need to work with time zones, it is recommended that you use the timestamp data type discussed in the Section called Timestamps ." This is primarily because of the fact that, due to daylight savings, time zones cannot always be meaningfully interpreted without an associated date.

Internally, PostgreSQL keeps track of all time zone information as a numeric offset of GMT (Greenwich Mean Time), which is also known as UTC (Universal Coordinated Time). By default, PostgreSQL's time display will use the time zone that your server's operating system is configured for. If you wish the time value to operate under a different time zone, there are four ways in which you can modify the output:

Timestamps

The PostgreSQL timestamp combines the functionality of the PostgreSQL date and time types into a single data type. The syntax of a timestamp value consists of a valid date format, followed by at least one whitespace character, and a valid time format. It can be followed optionally by a time zone value, if specified.

Combinations of all date and time formats listed in Table 3-15 and Table 3-20 are each supported in this fashion. Table 3-22 illustrates some examples of valid timestamp input.

Intervals

The SQL92 standard specifies a data typed called an interval , which represents a fixed span of time. By itself, an interval represents only a quantity of time , and does not begin or end at any set date or time. These intervals can be useful when applied to date and time values to calculate a new date or time, either by subtracting or adding the quantity. They can also be handy for quickly determining the precise interval between two date or time values. This can be achieved by subtracting date values, time values or timestamps from one another.

The two syntax variations below can specify an interval within PostgreSQL:

  
qty
 
unit
  [ ago ]
  
qty1
 
unit
 [, 
qty2
 
unit2
 ... ] [ ago ]

Where:

Example 3-27 shows functional syntax for date and interval values being meaningfully combined. You can see that subtracting an inverted time interval (e.g., one with the term ago) is functionally identical to adding a normal interval. This can be thought of as similar to the effect of adding negative numbers to integer values.

booktown=# 
SELECT date('1980-06-25');

    date
------------
 1980-06-25
(1 row)

booktown=# 
SELECT interval('21 years 8 days');

    interval
-----------------
 21 years 8 days
(1 row)

booktown=# 
SELECT date('1980-06-25') + interval('21 years 8 days') 

booktown-# 
AS spanned_date;

      spanned_date
------------------------
 2001-07-03 00:00:00-07
(1 row)

booktown=# 
SELECT date('1980-06-25') - interval('21 years 8 days ago') 

booktown-# 
AS twice_inverted_interval_date;

 twice_inverted_interval_date
------------------------------
 2001-07-03 00:00:00-07
(1 row)

Built-in date and time constants

PostgreSQL supports many special constants for use when referencing dates and times. These constants represent common date/time values, such as now , tomorrow , and yesterday . The predefined date and time constants supported by PostgreSQL are listed in Table 3-23.

PostgreSQL also provides three built-in functions for retrieving the current time, date, and timestamp. These are aptly named current_date, current_time, and current_timestamp.

The now and current timestamp constants may seem to be identical, looking solely at their names. They are, however, very different in terms of storing them in a table. The now constant is translated into the timestamp of the system time at the execution of whichever command referenced it (e.g., the time of insertion, it now had been referenced in an INSERT statement). In contrast, the current constant, as it is a deferred identifier, will actually appear as the phrase current in the database. From there, it can be translated (e.g., via the to_char() function) to the timestamp associated with the transaction time of any query which requests that value .

In other words, current will always tell you the "current" time when queried, regardless of when it was stored to the table. The current constant can be used in special situations, such as process tracking, where you may need to calculate the difference between a timestamp made with now and the current date and time to find the total time the process has been running. Example 3-28 demonstrates using the now and current constants to create a log of tasks. First, a table is created to house the task's name, its start date and time, and its finished date and time. Two tasks are then added to the table, using the now constant to set the start date and current to set the completed date. The reason this is done is to show that both of these tasks are uncompleted. If a task were to be completed, the table could be updated to show a now timestamp for that task's timefinished column.

booktown=# 
CREATE TABLE tasklog

booktown=# 
  (taskname char(15),

booktown=# 
   timebegun timestamp,

booktown=# 
   timefinished timestamp);

CREATE
booktown=# 
INSERT INTO tasklog VALUES

booktown=# 
   ('delivery', 'now', 'current');

INSERT 169936 1
booktown=# 
INSERT INTO tasklog VALUES

booktown=# 
   ('remodeling', 'now', 'current');

INSERT 169937 1
booktown=# 
SELECT taskname, timefinished - timebegun AS timespent FROM tasklog;

    taskname     | timespent
-----------------+-----------
 delivery        | 00:15:32
 remodeling      | 00:04:42
(2 rows)

Therefore, you generally want to use now when storing a transaction timestamp in a table, or even the current_timestamp function, which is equivalent to the output of now . Example 3-29 shows how this could be a potentially disastrous SQL design issue if not properly understood. It shows a pair of INSERT statements; one which uses now , another which uses current. If you watch the first row returned from the two queries (the row with a current timestamp), you'll notice it changes in each query to show the updated system time, while the second row remains the same (this is he the row in which now was used).

booktown=# 
INSERT INTO shipments (customer_id, isbn, ship_date) 

booktown-# 
       VALUES (1, '039480001X', 'current');

INSERT 3391221 1
booktown=# 
INSERT INTO shipments (customer_id, isbn, ship_date) 

booktown-# 
       VALUES (2, '0394800753', 'now');

INSERT 3391222 1
booktown=# 
SELECT isbn, ship_date FROM shipments;

    isbn    |       ship_date
------------+------------------------
 039480001X | current
 0394800753 | 2001-08-10 18:17:49-07
(2 rows)

booktown=# 
SELECT isbn, 

booktown-# 
       to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') 

booktown-# 
       AS value

booktown-# 
  FROM shipments;

    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:21:22
 0394800753 | 2001-08-10 18:17:49
(2 rows)

booktown=# 
SELECT isbn, to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') AS value

booktown-# 
  FROM shipments;

    isbn    |        value
------------+---------------------
 039480001X | 2001-08-10 18:22:35
 0394800753 | 2001-08-10 18:17:49
(2 rows)