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

In addition to these functions, there are many special constants which are supported by PostgreSQL. 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-24 .

Table 3-24. Date and Time Constants

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 relevant transaction time (e.g., the time of insertion of an INSERT statement). In contrast, the current constant, as it is a deferred identifier, will actually appear as the phrase 'current' in the database if inserted. 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.

Therefore, you generally always 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 .

Example 3-29. Comparing now to current

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)