Overview

In this article, we will cover the most commonly used PostgreSQL date and time functions that allows working with date and time values effectively.

Age()

In business scenarios where we need to calculate the ages of people, service tenure of employees, tenure of students in school/universities, etc., we can use the Age() function to achieve these tasks. The basic syntax is:

select AGE(timestamp,timestamp);
Select AGE(timestamp):

In the first example, the AGE() function accepts two TIMESTAMP values in YYYY-MM-DD format. The second argument is subtracted from the first one and an interval is returned as result.

In the second syntax when only the TIMESTAMP is passed as an argument, age() subtracts from the current date.

Here are some examples with their results:

SELECT AGE('2021-01-01','2018-10-24');

SELECT AGE(timestamp '1989-03-09');

CURRENT DATE

The CURRENT_DATE function returns the current date. The function does not take an argument. The syntax is:

SELECT CURRENT_DATE;

The CURRENT_DATE function is normally used as default value of a column. Let us take the following example to understand the scenario:

CREATE TABLE registration(
    student_id serial PRIMARY KEY,
    student_name varchar(255) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE
);

In the above table, we have the column 'registration_date' whose value will be defined by CURRENT_DATE function. Let us now insert a record in the table to see how it works,

INSERT INTO registration(student_name)
VALUES('Shivayan Mukherjee');

In the statement, we only specified a value for the student_name column. The other columns inserted the default values, one of which is the current date.

CURRENT_TIME

The CURRENT_TIME function returns the current time along with the local timezone. The function takes an optional argument, called precision. The syntax is:

SELECT CURRENT_TIME;

In here the first half of the output depicts the time in 24hr format. Since no precision is provided as argument in the query, the decimal value up to 6 places is returned. +05:30 is the local time zone of India.

In the second example, 2 is the precision passed as an argument. Therefore, the value up to 2 decimal places is returned in the output.

SELECT CURRENT_TIME(2);

Similar to the CURRENT_DATE function, the CURRENT_TIME function is also used to insert default value in columns where there is a requirement to track the system time for the entries. Let us take the following example to understand the scenario:

CREATE TABLE registration(
    student_id serial PRIMARY KEY,
    student_name varchar(255) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_time TIME DEFAULT CURRENT_TIME
);

Let us now insert a record in the table

INSERT INTO registration(student_name)
VALUES('Shivayan Mukherjee');

In the statement, we only specified a value for the student_name column. The other columns had the default values inserted.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time along with the local timezone. As this function captures both the date and time together, it is the preferred mode of operation over individual date and time functions in programming world. The function takes an optional argument called precision. Here is the syntax:

SELECT CURRENT_TIMESTAMP;

In this example, the first half of the output depicts the date in YYYY-MM-DD followed by time in 24hr format and since no precision is provided as argument in the query, the decimal value up to 6 places is returned. +05:30 is the local time zone of India.

In the next example, 2 is the precision passed, and therefore the value to 2 decimal places is returned in the output.

SELECT CURRENT_TIMESTAMP(2);

The CURRENT_TIMESTAMP can be used to insert default value in columns where there is a necessity to track the timestamp for the entries. Let us take the following example to understand the scenario:

CREATE TABLE registration(
    student_id serial PRIMARY KEY,
    student_name varchar(255) NOT NULL,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let us now insert a record in the table

INSERT INTO registration(student_name)
VALUES('Shivayan Mukherjee');

In the statement, we only specified a value for the student_name column. The other columns had the default values inserted.

We can see the registered_at column was populated by the date and time at which the statement executed.

Conclusion

This article gives an overview on some of the date and time functions in PostgreSQL. We present to you some real time queries to show how to use these functions in programming world. We hope this article will help you get started on your journey.