Using PostgreSQL now() function

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)