Overview

In this article, we will cover the most commonly used PostgreSQL date and time functions: LOCALTIMESTAMP(), LOCALTIME(), TO_DATE(), TO_TIMESTAMP(). If you are not yet familiar with any date and time functionality my suggestion would be to go through Part  1 of this PostgreSQL Date And Time tutorial series to get started.

LOCALTIMESTAMP()

The LOCALTIMESTAMP() function returns the current date and time without the local time zone. The function takes an optional argument, called precision. The basic syntax is:

SELECT LOCALTIMESTAMP;

The results of the first half of the output depict the date followed by time in 24hr format. Since no precision is provided as an argument,  up to 6 decimal places are returned.

In this example, numeric 2 value of the precision passed in the argument, therefore up to 2 decimal places are returned in the output.

SELECT LOCALTIMESTAMP(2);

In programming world the LOCALTIMESTAMP function is often used to insert default values in columns that track the current date and time for the records inserted, for example in audit columns.

Let us take the following example to understand the scenario. In this table, registration_datetime is the audit column.

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

Let us now insert a record in the table

INSERT INTO registration(student_name)
VALUES('James Watt');

We see here along with the user provided data , the audit column was also populated with the current timestamp.

LOCALTIME()

The LOCALTIME() function returns the current time without the local time zone. The function takes an optional argument, called precision. The basic syntax is:

SELECT LOCALTIME;

In the results, the first half of the output depicts the time in 24hr format. Since no precision is provided as argument in the query, up to 6 decimal places are returned.

In this example, numeric 2 value of the precision passed in the argument therefore up to 2 decimal places are returned in the output.

SELECT LOCALTIME(2);

Similar to the LOCALTIMESTAMP function, the LOCALTIME function is also often used to insert default values in columns that track the current time for the records inserted, again, in audit columns.

Let us take the following example to understand the scenario. In this table, registration_date and registration_time are the audit columns.

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

Next let us insert a record in the table

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

We see here, along with the user provided data , the audit columns also got populated with the current date and time.

TO_DATE

The TO_DATE() function helps to convert a string to a date and is perhaps one of the most important and extensively used PostgreSQL date/time functions in programming world. The TO_DATE() function converts a string literal to a date value. The basic syntax is:

TO_DATE(text,dateformat);

The TO_DATE() function accepts two parameters. The first parameter is the string that you want to convert to a date. The second parameter is the date format. The TO_DATE() function returns a date value.

Example 1:

In this example the input string is converted into a date as per the input format 'YYYYMMDD' where YYYY is the year in 4 digits format, MM is the month in 2 digits format and DD is the date in 2 digits format.

SELECT TO_DATE('20210910','YYYYMMDD');

Example 2:

The important thing to note here is the input text must match the specified date format. The following query will work:

SELECT TO_DATE('10 Sep 2021', 'DD Mon YYYY');

This query will throw an error as the month and year in the string to not match the formatting.

SELECT TO_DATE('10 Sep 2021', 'DD YYYY Mon');

TO_TIMESTAMP()

The TO_TIMESTAMP() function helps to convert a string to a timestamp. The TO_TIMESTAMP() function converts a string literal to a timestamp based on a specific format. The basic syntax is:

TO_TIMESTAMP(text, timestampformat)

The TO_TIMESTAMP() function accepts two parameters. The first parameter is the string that you want to convert to a timestamp. The second parameter is the timestamp format. The TO_TIMESTAMP() function returns a timestamp with time zone.

Example 1:

In this example the input string is converted into a date as per the input format 'YYYY-MM-DD HH:MI:SS' where YYYY is the year in 4 digits format, MM is the month in 2 digits format and DD is the date in 2 digits format followed by time in hour, minutes and seconds format.

SELECT TO_TIMESTAMP(
    '2021-09-10 11:30:20',
    'YYYY-MM-DD HH:MI:SS'
);

Example 2: Time in 24 hr format

The expression for Time in example 1 supports 12 hr time format, to support 24 hr time format the expression 'HH24' must be used as shown in this example.

SELECT TO_TIMESTAMP(
    '2021-09-10 21:30:20',
    'YYYY-MM-DD HH24:MI:SS'
);

The results:

Conclusion

This article gives an overview on some of the most important and commonly used 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 explore and learn more on PostgreSQL date and time behaviour.