FunctionDescription and CommentsExample
CURRENT_DATEReturn the date in which the current transaction started
Returns DATE		SELECT CURRENT_DATE;
=> 2012-03-26
CURRENT_TIMEReturn current time (start of current transaction)
Returns TIME		SELECT CURRENT_TIME;
=> 11:31:46.903633
CURRENT_TIMESTAMPReturns current timestamp (start of current transaction)
Returns TIMESTAMP
See notes		SELECT CURRENT_TIMESTAMP;
=> 2012-03-26 11:31:46.903633
NOW()Returns current date and time (start of current transaction)
Returns TIMESTAMP
See notes		SELECT NOW();
=> 2012-03-26 11:31:46.903633
EXTRACT(unit FROM TIMESTAMP)Extracts the specified unit (date or time part) from a date, time, or timestamp
Returns DECIMAL
See notes		SELECT EXTRACT(YEAR FROM TIMESTAMP '2012-03-26 11:31:46.903633');
=> 2012

SELECT EXTRACT('day' FROM DATE '2012-03-26');
=> 26
DATE_PART(unit, TIMESTAMP)Similar to EXTRACT()
Returns DECIMAL
See notes		SELECT DATE_PART('day', TIMESTAMP '2012-03-26 11:31:46.903633');
=> 26

SELECT DATE_PART('minute', TIME '11:31:46.903633');
=> 31

Notes:

  1. NOW() and CURRENT_TIMESTAMP: When using those functions as part of an INSERT/UPDATE command, the returned value is converted into the column data type it is writing to. When writing into TIME data type, only the time is written, when writing into DATE data type, only the date is written, and when writing to TIMESTAMP data type, the whole timestamp is written.

    Note that NOW() and CURRENT_TIMESTAMP refer to the time the transaction starts, and not to each statement time inside the transaction

  2. DATE_PART() and EXTRACT() provide similar functionality and accept the same set of units. However, DATE_PART requires the unit argument to be passed as a string (e.g. ‘YEAR”, ‘day’), while EXTRACT() also accepts these units as keywords. The following units are supported:

Time UnitsDate Units
MICROSECONDSDAY
MILLISECONDSWEEK
SECONDMONTH
MINUTEQUARTER
HOURYEAR
DECADE
CENTURY
MILLENNIUM

For a TIMESTAMP object, both the Date and Time units are supported.