8 PostgreSQL Date and Time Function Examples

In this article, let us review following PostgreSQL date and time functions with practical examples.

• now()
• now()::date
• now()::time
• date_part()
• age()
• extract()
• date_trunc()
• to_char()
• to_timestamp()

1. Get Current Date and Time using PostgreSQL now()

Get current date and time using the following query.

dbase=# select now();
             now
------------------------------
 2010-06-19 09:28:43.98216-07
(1 row)

Use the ::time as shown below to get only the time (without date).

dbase=# select now()::time;
      now
----------------
 09:29:12.19297
(1 row)

Use the ::date as shown below to get only the date (without time).

dbase=# select now()::date;
    now
------------
 2010-06-19
(1 row)

2. Get Interval Between Two PostgreSQL Dates

In order to get the number of days between two dates, use the ‘-‘ operator. This works like an arithmetic operator.

Let us assume that the employee table contains following records.

# select name,date_of_join from employee;
   name  | date_of_join
--------+------------
 Neeraj | 2002-11-23
 Kiran  | 2003-01-01
 Sam    | 2005-11-23
 John   | 2006-01-01
(4 rows)

Following examples displays number of days between the current date and date_of_join of employees.

dbase=#  select now() - date_of_join as days from employee ;
           days
---------------------------
 2762 days 08:52:33.436868
 2723 days 08:52:33.436868
 1666 days 08:52:33.436868
 1627 days 08:52:33.436868
(4 rows)

You can also subtract numeric value from the date. This subtracts number of days from a specific date. The following query subtracts 7 days from the date_of_join field value.

dbase=# select date_of_join – 7 as output from employee;
   output
------------
 2002-11-16
 2002-12-25
 2005-11-16
 2005-12-25
(4 rows)

3. Round the interval (above difference) to the nearest day using date_part()

Use the following query to round the number of days to the nearest day.

dbase=# select date_part('days', now() - date_of_join) as days from employee;
 days
 ------
 2762
 2723
 1666
 1627
(4 rows)

4. Breakdown the date interval into number of years, months and days using age()

There is the another way to find the interval between the current date and date_of_join as shown below.

dbase=# select age(date_of_join) from employee;
          age
------------------------
 7 years 6 mons 23 days
 7 years 5 mons 15 days
 4 years 6 mons 23 days
 4 years 5 mons 15 days
(4 rows)

5. Retrieve any sub-fields from the Timestamp using PostgreSQL extract()

Postgres date functions allows you to extract the specific sub-field form the date. Following query extracts the year from date_of_join field.

dbase=# select extract(year from date_of_join) as output from employee;
 output
--------
   2002
   2003
   2005
   2006
(4 rows)

6. Truncate a particular date field using PostgreSQL date_trunc()

Postgres provides the facility to truncate the date to specific precision. The following query gives you start date of the month based on the value in the date_of_join field.

dbase=# select date_trunc('month',date_of_join) as output from employee ;
       output
------------------------
 2002-11-01 00:00:00-08
 2003-01-01 00:00:00-08
 2005-11-01 00:00:00-08
 2006-01-01 00:00:00-08
(4 rows)

7. Display Postgresql Date in Various Format using to_char()

The following query displays the date in “dd/mm/yy” format.

dbase=# select to_char(date_of_join,'mm/dd/yy') as output from employee;
 output
----------
 11/23/02
 01/01/03
 11/23/05
 01/01/06
(4 rows)

The month and day of date_of_join field can be displayed as shown below.

dbase=# select to_char(date_of_join, 'FMMonth FMDDth') as output from employee;
   output
---------------
 November 23rd
 January 1st
 November 23rd
 January 1st
(4 rows)

Display the full abbreviation of day and month as shown below.

dbase=# select to_char(startdate, 'Dy (Day), Mon (Month)') as output from employee;
              output
----------------------------------
 Sat (Saturday ), Nov (November )
 Wed (Wednesday), Jan (January  )
 Wed (Wednesday), Nov (November )
 Sun (Sunday   ), Jan (January  )
(4 rows)

8. Convert String to Date using PostgreSQL to_timestamp()

Postgres provides the way to convert a string value into proper date format as shown below.

dbase=# select to_timestamp('201024June10:12am', 'YYYYDDFMMonthHH12:MIam') as valid_time;
       valid_time
------------------------
 2010-06-24 10:12:00-07
(1 row)