PostgreSQL offers many powerful and convenient date and time functions for working with dates and times. These functions assist the users in manipulating the date and time values efficiently. For instance, storing a date or time value in a database, getting the current date or time, extracting a specific date or time part, etc.

In this Postgres blog, we are going to discuss the most frequently used date and time functions via Practical examples:

Date/Time Functions in PostgreSQL With Examples

This section will explain the working of the below-listed frequently used date-time functions via syntax and examples:

• CURRENT_DATE
• CURRENT_TIME
• NOW()
• TIMEOFDAY()
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DATE_PART()
• DATE_TRUNC()
• EXTRACT()
• TO_DATE()
• ISFINITE()
• AGE()
• TO_TIMESTAMP()

So, let’s begin with the “CURRENT_DATE” function.

1) Postgres CURRENT_DATE Function

The below-mentioned points illustrate the working of the Postgres CURRENT_DATE function:

- In PostgreSQL, the CURRENT_DATE function is used to get the current/today’s date and time.
- It retrieves the date in “YYYY-MM-DD” format.

Basic syntax

Follow the below syntax to avail the functionality of the CURRENT_DATE function:

CURRENT_DATE;

The CURRENT_DATE function doesn’t accept any argument.

Return Type: “DATE”.

Example: How to Use CURRENT_DATE in Postgres?

The below snippet explains the working of the CURRENT_DATE function:

SELECT CURRENT_DATE;

The output shows that the CURRENT_DATE function retrieves the current date in “YYYY-MM-DD” format.

2) CURRENT_TIME Function

The following points illustrate how the Postgres CURRENT_TIME function works:

- As the name suggests, Postgres’ CURRENT_TIME function is used to get the current time.
- It can accept an optional parameter “precision” that is used to specify the fractional seconds’ precision.
- It retrieves the time along with the time zone.

Basic syntax

Follow the below syntax to avail the functionality of the CURRENT_TIME function:

CURRENT_TIME;

Return Type: “TIMESTAMPTZ”.

Example: How to Use CURRENT_TIME in Postgres?

The below snippet explains the working of the CURRENT_TIME function:

SELECT CURRENT_TIME;

The output shows that the targeted function retrieves the current time and the time zone.

3) Postgres NOW() Function

Consider the following points to comprehend the working of the NOW() function:

- Postgres offers an inbuilt date function named NOW() that retrieves the current date, time, and timezone.
- The NOW() function doesn’t require any argument.
- It retrieves the date in the “YYYY-MM-DD HH-MM-SS.MS-TZ” format.

Basic Syntax

You must follow the below syntax to use the NOW() function in Postgres:

NOW();

Return Type: TIMESTAMPTZ.

Example: How Does the NOW() Function Work in Postgres?

The below-stated piece of code will show you the current date, time, and time zone based on your system’s date:

SELECT NOW();

The output proves the working of Postgres’ NOW() function.

4) Postgres TIMEOFDAY() Function

The following points will assist you in understanding the working of Postgres’ TIMEOFDAY() function:

- In Postgres, TIMEOFDAY() is a built-in date function that retrieves the current date and time.
- It retrieves the date/time in “Day Mon DD HH-MM-SS.MS YYYY TZ” format.

Basic Syntax

You must follow the below syntax to use the TIMEOFDAY() function in Postgres:

TIMEOFDAY();

Return Type: TEXT.

Example: How Does the TIMEOFDAY() Function Work in Postgres?

Execute the below line of code to obtain the date-time in “Text” format:

SELECT TIMEOFDAY();

The output shows that the TIMEOFDAY() function retrieves the current date and time in TEXT format.

5) Postgres CURRENT_TIMESTAMP Function

The below-listed points elaborate on the working of the CURRENT_TIMESTAMP function:

- The CURRENT_TIMESTAMP function in Postgres retrieves the current date, time, and timezone at which a transaction begins.
- It retrieves the date/time in the “YYYY-MM-DD HH-MM-SS.MS-TZ” format.

Basic Syntax

The syntax for using the CURRENT_TIMESTAMP is as follows:

CURRENT_TIMESTAMP;

Return Type: TIMESTAMPTZ.

Example: How to Use CURRENT_TIMESTAMP in Postgres?

Executing the below line of code will show you how the CURRENT_TIMESTAMP function works in Postgres:

SELECT CURRENT_TIMESTAMP;

The output snippet shows that the targeted function retrieves the timestamp with the time zone.

6) Postgres LOCALTIMESTAMP Function

The below-listed points will help you understand the working of the LOCALTIMESTAMP function:

- Another widely used built-in function is LOCALTIMESTAMP, which retrieves the time and date at which the current transaction began.
- It retrieves the date/time in the “YYYY-MM-DD HH-MM-SS.MS” format.

Basic Syntax

LOCALTIMESTAMP function can be accessed using the following syntax:

LOCALTIMESTAMP;

Return Type: TIMESTAMP.

Example: How to Use the LOCALTIMESTAMP Function in Postgres?

Run the following line of code to get the current date and time without the time zone:

SELECT LOCALTIMESTAMP;

The output shows that the specified function retrieves the date and time without a time zone.

7) Postgres DATE_PART() Function

DATE_PART() is a built-in function in Postgres that is used to get only a specific part/field of timestamp, date, interval, etc.

- It accepts a field and a source as arguments.
- The value for the field argument should be valid.

Basic Syntax

The syntax for using the DATE_PART() function is as follows:

DATE_PART(field, source);

The above snippet shows that the DATE_PART() function accepts two arguments: a field and a source. The DATE_PART() function will extract/pull out the field from the specified source.

Return Type: DOUBLE PRECISION.

Example: How Does the DATE_PART() Function Work in Postgres?

Suppose we want to pull out a year from a specific date; for this purpose, we will use the DATE_PART() function as follows:

SELECT DATE_PART('Year', TIMESTAMP '2022-12-09');

The output snippet shows that the DATE_PART() function pulls out the year from the given date.

8) Postgres DATE_TRUNC() Function

In PostgreSQL, DATE_TRUNC() is a built-in date function that truncates/trims the unnecessary part from the date/time.

- It accepts two arguments, a datePart, and a field.
- The value for the field parameter must be valid.
- It retrieves the trimmed part with a specific precision level.
- Using the DATE_TRUNC() function, all values of the given field except the specified date will be reset to their initials.

Basic Syntax

Use the below syntax to avail the services of the DATE_TRUNC() function:

DATE_TRUNC(datePart, field);

The datePart must be valid, as described in the “DATE_TRUNC() Function in Postgres” blog. While in place of a field argument, you can specify an INTERVAL or TIMESTAMP.

Return Type: TIMESTAMP.

Example: How Does Postgres' DATE_TRUNC() Work?

Let’s learn how the DATE_TRUNC() function work in Postgres:

SELECT DATE_TRUNC('Year', TIMESTAMP '2022-08-02 11:12:14');

The output shows that the DATE_TRUNC() function resets all the values of the input timestamp(other than the specified date part, i.e., “Year” in the above example) to their initials.

9) Postgres EXTRACT() Function

In Postgres, the built-in EXTRACT() function works the same way as the DATE_PART() function:

- It is used to extract a particular field from the input TIMESTAMP.
- The return type of the EXTRACT() function is DOUBLE PRECISION.

Basic Syntax

The basic syntax of the EXTRACT() function will go like this:

EXTRACT(Field FROM Source);

The above syntax shows that the EXTRACT() function accepts two arguments: a field and a source. The EXTRACT() will extract the specified field from the given source.

Example: How Do I Use the EXTRACT() Function in Postgres?

Suppose we need to extract the minutes from the current date/time; to do that, we will utilize the EXTRACT() function as follows:

SELECT EXTRACT('MIN' FROM NOW());

The output snippet shows that minutes were successfully extracted from the current date and time.

10) Postgres TO_DATE() Function

Postgres offers an in-built TO_DATE() function that is used to convert a string/text into a date:

- It accepts a string and a valid date format as arguments.
- The date format must be a valid pattern/format, as demonstrated in the following blog: “Postgres’ TO_DATE() Function”.
- It converts the input string into a date according to the defined format.

Basic Syntax

Postgres' TO_DATE() function can be accessed using the following syntax:

TO_DATE(string, date_format);

The above snippet indicates that the TO_DATE() function accepts a string and a date_format as an argument. Consequently, it will convert the input string into a date based on the specified date_format.

Return Type: “DATE”.

Example: How Do I Use the TO_DATE() Function in Postgres?

To demonstrate how the TO_DATE() function works, let's pass a string and a date format as arguments:

SELECT TO_DATE('2022-12-09','YYYY-MM-DD');

The output authenticates that the given string has been converted into the DATE data type.

11) Postgres ISFINITE() Function

In Postgres the ISFINITE() Function is used to check if a date/time/interval is finite or not:

- It accepts a date, time, or an interval as an argument and retrieves a boolean value.
- Retrieving a “TRUE” value means the specified date, time or interval is finite.
- Retrieving a “FALSE” value means the specified date, time or interval is infinite.

Basic Syntax

Postgres' ISFINITE function can be accessed using the following syntax:

ISFINITE(TIME | DATE | INTERVAL);

The return type of the ISFINITE() function is Boolean.

Example: How to Use the ISFINITE Function in Postgres?

In this example, we will pass the NOW() function as an argument to the ISFINITE() function.

SELECT ISFINITE(NOW());

The output shows that the given argument is finite.

12) Postgres AGE() Function

Postgres provides a built-in AGE() function that accepts two dates as arguments and finds the interval between the given dates. The second time stamp will be subtracted from the first one, and the resultant interval will be returned as output.

Basic Syntax

Use the following syntax to get the services of the Postgres AGE() function:

AGE(TIMESTAMP_1, TIMESTAMP_2);

Return Type: INTERVAL.
Example: How Do I Use the AGE() Function in Postgres?

Let’s suppose we want to calculate the age of a person whose birth date is “1990-01-01”. To do that, we will execute the AGE() function as follows:

SELECT AGE(CURRENT_DATE, '1990-01-01');

The output shows that the AGE() function calculates the age and retrieves an INTERVAL.

13) Postgres TO_TIMESTAMP() Function

In Postgres, the built-in TO_TIMESTAMP() function is used for the string-to-timestamp conversion.

- It accepts a string and a valid format as arguments.
- The format must be valid, as described in this blog post.

Basic Syntax

The basic syntax of the TO_TIMESTAMP() function will go like this:

TO_TIMESTAMP(timestamp, format);

The above syntax shows that the TO_TIMESTAMP() function accepts two arguments: a timestamp(as a string) and a format. The input string will be converted into a timestamp according to the defined format.

Return Type: TIMESTAMPTZ.

Example: How Does the TO_TIMESTAMP() Function Work in Postgres?

Suppose we need to convert a “2022-12-12” string to a timestamp; for that purpose, we will execute the TO_TIMESTAMP function as follows:

SELECT TO_TIMESTAMP('2022-12-12', 'YYYY-MM-DD');

The output snippet shows that the input string has been converted into a timestamp.

That’s it from this Postgres blog.

Conclusion

A wide range of built-in functions is available in PostgreSQL for working with dates and times. Using these functions, users can manipulate date and time values efficiently. For instance, functions like NOW(), CURRENT_DATE, CURRENT_TIME, etc., are used to get the current date or time. The AGE() function is used in Postgres to find a specific interval. While to extract a specific date-time part, the functions like EXTRACT(), DATE_PART(), etc., are used in Postgres. This Postgres blog has explained different date-time functions with practical examples.