Working with dates in PostgreSQL

Get the current date

In PostgreSQL, you can get the current date and time by using the built-in NOW() function. The following statement will return both the day and time:

SELECT NOW();

              now
-------------------------------
 2021-09-27 15:22:53.679985+02
(1 row)

If the time is not of interest, you can also specify to only return the date with double colons :: to cast a DATETIME value to the DATE value:

SELECT NOW()::date;

    now
------------
 2021-09-27
(1 row)

Using CURRENT_DATE is another way to get the current date as demonstrated below:

SELECT CURRENT_DATE;

 current_date
--------------
 2021-09-27
(1 row)

All three of these options will return you the date in the yyyy-mm-dd format. Within PostgreSQL, you can adjust the format of this output if desired.

Output a date value in a specific format

To output a date value in a specific format, you use the TO_CHAR() function. This function accepts two parameters:

1. the value you want to format
2. the template that defines the output format

SELECT TO_CHAR(NOW()::date, 'dd/mm/yyyy');

  to_char
------------
 27/09/2021
(1 row)

You can also display the date in a format like Sep 27, 2021:

SELECT TO_CHAR(NOW():: DATE, 'Mon dd, yyyy');

   to_char
--------------
 Sep 27, 2021
(1 row)

Depending on the requirements of a system, you may need a date formatted in a specific way. This is a scenario where being able to specify the output in PostgreSQL is useful.

Get the interval between two dates

PostgreSQL allows you to get the interval between two dates using the - operator. Using this operator allows you to calculate things like the tenure of an employee or time since the publishing of a book.

In our example we want to find how many days it's been since Joyce's Ulysses was published by subtracting the current date from the published_date:

SELECT
                author_name,
                book_title,
                now()::date - published_date as diff
FROM
                checkouts;

Resulting in:

 author_name | book_title |            diff
-------------+------------+----------------------------
 James Joyce |  Ulysses   |         36397 days
(1 row)

Calculating age using date values

We can continue with the same example to calculate the age at the current date in years, months, and days using the AGE() function. The following statement uses the AGE() function to calculate the age of a publication from our library checkouts tables:

SELECT
                author_name,
                book_title,
                AGE(published_date)
FROM
                checkouts;

With this function we can calculate how old a book in inventory is:

 author_name | book_title |           age
-------------+------------+-------------------------
 James Joyce |  Ulysses   | 99 years 7 mons 25 days
(1 row)

It is important to note that if you pass a single date into the AGE() function, then it will automatically use the current date to subtract and calculate. You are also able to pass two dates into the function to calculate age such as:

SELECT
                author_name,
                book_title,
                AGE('2000-01-01',published_date),
FROM
                checkouts;

Resulting in:

 author_name | book_title |           age
-------------+------------+--------------------------
 James Joyce |  Ulysses   | 77 years 10 mons 27 days
(1 row)

Extracting year, quarter, month, week, or day from a date value

The last function that we are going to cover is the EXTRACT() function in PostgreSQL that allows you to separate the components of date like the year, quarter, month, and day.

The following statement pulls out the year, month, and day from the published date of Ulysses:

SELECT
            author_name,
            book_title,
            EXTRACT(YEAR FROM published_date) AS YEAR,
            EXTRACT(MONTH FROM published_date) AS MONTH,
            EXTRACT(DAY FROM published_date) AS DAY
FROM
            checkouts;

The results will look like the following:

 author_name | book_title | year | month | day
-------------+------------+------+-------+-----
 James Joyce |  Ulysses   | 1922 |   2   |  2
(1 row)

This is a useful function to be aware of when you may only need a portion of a date value for a calculation with your data for example.