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 current transaction time, deferred
1970-01-01 00:00:00+00 (UNIX's "Birthday")
An abstract constant later than all other valid dates and times
An abstract constant earlier than all other valid dates and times
The current transaction timestamp
Midnight, on the current day
Midnight, on the day after the current day
Midnight on the day before the current day
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)