8 PostgreSQL Date and Time Function Examples

by Balakrishnan Mariyappan on July 8, 2010

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)

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 6 comments… read them below or add one }

1 Tanmay Joshi July 9, 2010 at 6:32 am

Nice refresh :)

2 Ali Naser June 30, 2011 at 7:55 am

I have two times like starttime = 07:40 AM and endtime = 11:05 AM as string/text field
I want to check a time suppose 08:40 AM is between starttime and endtime is true or false

3 Rick August 27, 2011 at 9:01 am

Hi Ali,

use:

Select ’08:40′::time BETWEEN ’07:40′::time AND ’11:05′::time

that’s all…

4 birju August 29, 2011 at 8:04 am

thanks..thanks a lot

5 Celso Soares September 20, 2011 at 7:41 pm

that complex solution worked for me:

now()::time BETWEEN to_timestamp(ps.contact_period_since || ”, ‘HH24′)::time AND to_timestamp(ps.contact_period_until || ”, ‘HH24′)::time

6 Alastair November 25, 2013 at 2:21 pm

Hi, I am looking to extract the number of completed months between a date in a table and todays date so that it can be used in another formula. I created a view (select age(date_in_table) from table;) but it shows years, months and days. Do you know how I would do this?

Thanks

Leave a Comment

Previous post:

Next post: