PostgreSQL Date/Time Functions with examples

Please refer the post Date/Time Data Type for Date/Time Data Types

Additional to date/time data types postgresql supplies a bunch of data/time functions too like AGE, EXTRACT, DATE_PART, DATE_TRUNC etc.

AGE

AGE is one of the useful date/time function supplied in PostgreSQL. AGE between two date gives you the difference in Years, Months and Days

postgres=# select AGE(current_date,'12-Jan-2020');
          age           
------------------------
 2 years 5 mons 24 days
(1 row)

AGE can be used with date and timestamp arguments

age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) → 43 years 9 mons 27 days

age ( timestamp, timestamp ) → interval

age ( date, date ) → interval

age ( timestamp ) → interval

age(timestamp ‘1957-06-13’) → 62 years 6 mons 10 days

EXTRACT

EXTRACT is another useful function, retrieves subfields such as year or hour from date/time values

Few examples given in the below tables

yearextract(year from timestamp ‘2022-07-05 19:09:15’) -> 2022
monthextract(month from timestamp ‘2022-07-05 19:09:15’) -> 7
dayextract(day from timestamp ‘2022-07-05 19:09:15’) ->5
hourextract(hour from timestamp ‘2022-07-05 19:09:15’) -> 19
minuteextract(minute from timestamp ‘2022-07-05 19:09:15’) -> 9
secondsextract(second from timestamp ‘2022-07-05 19:09:15.123456’) -> 15.123456
millisecondsextract(year from timestamp ‘2022-07-05 19:10:15’) -> 15123.456
centuryextract(century from now()) -> 21
dowday of week, extract(dow from now()) -> 2
doyday of year, extract(doy from now()) -> 186
epochextract(epoch from now()) -> 1657029477.791060
julianextract(julian from now()) -> 2459766.81384544296296296296
microsecondsextract(microseconds from timestamp ‘2022-07-05 19:09:15.123456’) -> 15.123456
quarterextract(quarter from now()) -> 3
timezoneextract(timezone from now()) -> 19800 ( i.e. 5:30 in seconds )
timezone_hourextract(timezone_hour from now()) -> 5
timezone_minuteextract(timezone_minute from now()) -> 30
weekextract(week from now()) -> 27

DATE_PART

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract. Date_Part and Extract functions more over same.

date_part(‘field’, source)

SELECT date_part(‘day’, TIMESTAMP ‘2001-02-16 20:38:40’) -> 16

SELECT date_part(‘day’, now()) -> 5 ( i.e today’s day)

DATE_TRUNC

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc(field, source [, time_zone ])

Valid values for field are:
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

SELECT date_trunc(‘day’, TIMESTAMP ‘2022-06-05 19:55:20’) -> 2022-06-05 00:00:00

SELECT date_trunc(‘year’, TIMESTAMP ‘2022-06-05 19:55:20’) -> 2022-01-01 00:00:00

DATE_BIN

The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin

date_bin(stride, source, origin)

SELECT date_bin(’15 minutes’, TIMESTAMP ‘2020-02-11 15:44:17’,TIMESTAMP ‘2001-01-01’)

-> 2020-02-11 15:30:00

SELECT date_bin(’15 minutes’, TIMESTAMP ‘2020-02-11 15:44:17’,TIMESTAMP ‘2001-01-01 00:02:30’)

-> 2020-02-11 15:32:30

Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time.

These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME(precision)

CURRENT_TIMESTAMP(precision)

LOCALTIME

LOCALTIMESTAMP

LOCALTIME(precision)

LOCALTIMESTAMP(precision)

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

CURRENT_TIMEselect CURRENT_TIME -> 20:13:37.337674+05:30
CURRENT_TIMESTAMPselect CURRENT_TIMESTAMP -> 2022-07-05 20:13:37.337674+05:30
LOCALTIMEselect LOCALTIME -> 20:16:00.1013
LOCALTIMESTAMPselect LOCALTIMESTAMP -> 2022-07-05 20:16:55.83616
NOW()select now() -> 2022-07-05 20:17:35.395143+05:30
CURRENT_DATEselect CURRENT_DATE -> 2022-07-05
CLOCK_TIMESTAMP()select CLOCK_TIMESTAMP() -> 2022-07-05 20:21:00.971717+05:30
TRANSACTION_TIMESTAMP()select TRANSACTION_TIMESTAMP() -> 2022-07-05 20:21:00.971717+05:30
STATEMENT_TIMESTAMP()select STATEMENT_TIMESTAMP() -> 2022-07-05 20:21:00.986545+05:30
TIMEOFDAY()select TIMEOFDAY() -> Tue Jul 05 20:22:32.235574 2022 IST

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called.

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns

statement_timestamp() returns the start time of the current statement

(more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.
timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual
current time, but as a formatted text string rather than a timestamp with time zone
value. now() is a traditional PostgreSQL equivalent to transaction_timestamp()

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: