PostgreSQL Date Functions

Understanding date and time functions in your database is essential for effective data analysis and reporting. Read on to learn more about PostgreSQL date functions.

This article covers some of the most useful PostgreSQL date and time functions and their applications in data analysis and reporting. SQL date functions facilitate different data analysis tasks, including sales analysis, financial reporting, website analytics, and more.

This article presents you with the tools, in the form of PostgreSQL date and time functions, used to accomplish these tasks. We’ll start with a quick overview of date and time data types available in PostgreSQL before moving on to the functions.

Check out our SQL Cookbook, which contains a variety of SQL recipes for PostgreSQL and other dialects. They’re a valuable resource worth bookmarking for quick reference when writing your queries. You can learn more about the concept of an SQL Cookbook here.

Date and Time Data Types in PostgreSQL

In PostgreSQL, there are various data types available for storing date and time values. Below is an overview of the most frequently used types:

For data analysis that must take into consideration different time zones, PostgreSQL offers data types like TIME WITH TIME ZONE (which stores time values with time zone information) and TIMESTAMP WITH TIME ZONE ( which stores date and time values with time zone information).

Read our article An Overview of PostgreSQL Data Types to learn more about data types available in PostgreSQL. And if you want to get more practice, check out these 19 exercises in PostgreSQL with detailed solutions.

We’ll make use of all the above data types while exploring PostgreSQL’s date and time functions. Let’s get started!

An Overview of Date and Time Functions in PostgreSQL

All date and time functions e in PostgreSQL can be grouped into the following categories:

• Comparing Date and Time Values
• Getting Current Date and Time Values
• Performing Arithmetic Operations on Date and Time Values
• Manipulating Date and Time Values
• Formatting Date and Time Values

The following sections list all relevant functions with examples.

This is the sales table that we’ll use for our examples:

Comparing Date and Time Values

To compare date and time values in PostgreSQL, we use standard comparison operators. These operators include =, !=, <>, >, >=, <, <=, BETWEEN, and NOT BETWEEN.

You can see usage examples for each comparison operator below.

Equal (=) Operator

Question: How many sales occurred on June 1, 2024?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_date = '2024-06-01';

Not Equal (!= or <>) Operator

Question: How many sales didn't occur at 10:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_time != '10:00:00';

Greater Than (>) Operator

Question: How many sales occurred after June 1, 2024, at 10:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp > '2024-06-01 10:00:00';

Note: When comparing dates in SQL, the > operator means that if date X is greater than date Y, date X is further into the future (later) than date Y.

Greater Than or Equal (>=) Operator

Question: How many sales occurred on or after 10:00:00 on June 1, 2024?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp >= '2024-06-01 10:00:00';

Note: When comparing timestamps in SQL, the >= operator means that if timestamp X is greater than or equal to timestamp Y, timestamp X is further into the future (later) than or equal to timestamp Y.

Less Than (<) Operator

Question: How many sales occurred before 12:00:00 on June 1, 2024?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp < '2024-06-01 12:00:00';

Note: When comparing timestamps in SQL, the < operator means that if timestamp X is less than timestamp Y,  X is earlier in time than Y.

Less Than or Equal (<=) Operator

Question: How many sales occurred on or before June 1, 2024, at 12:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp <= '2024-06-01 12:00:00';

Note: When comparing dates or times in SQL, the <= operator means that if date X is less than or equal to date Y, date X is earlier in time than or equal to date Y.

BETWEEN Operator

Question: How many sales occurred between 10:00:00 and 14:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

NOT BETWEEN Operator

Question: How many sales didn't occur between 10:00:00 and 14:00:00?

Answer:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp NOT BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Good to Know: You can sort the output by date, time, or timestamp simply by including the relevant column in the ORDER BY clause.

Check out these cookbooks to learn more:

• How to Compare 2 Dates in the WHERE Clause in SQL
• How to Compare Datetime Values in SQL
• How to Compare Date Values in SQL
• How to Order by Date in PostgreSQL or Oracle

Getting Current Date and Time Values

When analyzing data, we often want to compare it with the current timestamp every time the query is executed. In PostgreSQL, this can be accomplished with the NOW() function and the CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP keywords.

Important: The NOW() function is specific to PostgreSQL, whereas the CURRENT_* functions are provided in standard SQL and are recommended for use over the NOW() function.

NOW()

Use this function to select the current date and time along with the time zone.

SELECT NOW();

CURRENT_DATE

Use this keyword to select the current date.

SELECT CURRENT_DATE;

CURRENT_TIME

Use this keyword to select the current time along with the time zone (but without the date).

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP

Use this function to select the current date and time along with the time zone.

SELECT CURRENT_TIMESTAMP;

Check out these cookbooks to learn more:

• How to Get the Current Date in PostgreSQL
• How to Get the Current Time in PostgreSQL
• How to Get the Current Date and Time (No Time Zone) in PostgreSQL
• How to Get the Current Date and Time with Time Zone Offset in PostgreSQL
• How to Get Current Time (No Time Zone) in PostgreSQL

Arithmetic Operations on Date and Time Values

Arithmetic operations on date and time values in PostgreSQL include calculating the difference between two dates and adding or subtracting INTERVALs to or from date/time values.

Follow the usage examples below to:

Calculate the difference between two date values using the AGE() function.

SELECT AGE(sale_date, CURRENT_DATE) AS age_difference
FROM sales
WHERE product_name = 'Apples';

Note that this returns the INTERVAL between two values.

Calculate the difference between two date values using the minus (-) operator.

SELECT sale_timestamp - CURRENT_TIMESTAMP AS difference
FROM sales
WHERE product_name = 'Apples';

Note that this returns an interval between two values, as the age() function would.

Calculate the difference between two time values using the minus (-) operator.

SELECT sale_time, sale_time - '09:00:00' AS difference
FROM sales
WHERE product_name = 'Apples';

Add INTERVALs.

Use the plus (+) operator to add an INTERVAL to a date/time value:

SELECT sale_date + INTERVAL '1 day' AS next_day
FROM sales
WHERE product_name = 'Apples';

Subtract INTERVALs.

Use the minus (-) operator to subtract an INTERVAL from a date/time value.

SELECT sale_date - INTERVAL '1 week' AS previous_week
FROM sales
WHERE product_name = 'Apples';

Check out these cookbooks to learn more:

• How to Calculate Date Difference in PostgreSQL/Oracle
• How to Calculate Timestamp Difference in PostgreSQL
• How to Find the Interval Between Two Dates in PostgreSQL
• How to Get Yesterday’s Date in PostgreSQL

Manipulating Date and Time Values

PostgreSQL provides functions that facilitate extracting specific parts of date/time values, such as DATE_PART(), DATE_TRUNC(), and EXTRACT(). In data analysis, these functions are commonly used to group data by day, month, or year – thus enabling the creation of reports with statistics over defined time periods.

DATE_PART()

It extracts a specific component (e.g. year, month, or day) from a date/time value.

SELECT DATE_PART('year', sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

EXTRACT()

It extracts a specific part (e.g. year, month, or day) from a date/time value.

SELECT EXTRACT(YEAR FROM sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

The EXTRACT() function can be used interchangeably with the DATE_PART() function. However, EXTRACT() can handle time zones.

DATE_TRUNC()

It truncates a date/time value to the specified unit (e.g. year, month, or day).

SELECT DATE_TRUNC('year', sale_date) AS year_start
FROM sales
WHERE product_name = 'Apples';

MAKE_DATE()

It assembles a complete date from a year, month, and day.

SELECT MAKE_DATE('2024', '6', '1') AS date;

GENERATE_SERIES()

It generates a series of date/time values with equal intervals.

SELECT GENERATE_SERIES(
	   '2024-06-01 08:00:00'::timestamp, 
	   '2024-06-01 09:00:00'::timestamp, 
	   '20 minutes'::interval
             ) AS generated_timestamp;

Check out these cookbooks to learn more:

• How to Group by Month in PostgreSQL
• How to Group by Year in SQL
• How to Extract the Week Number from a Date in PostgreSQL
• How to Get Day Names in PostgreSQL
• How to Get the Day of the Year from a Date in PostgreSQL
• How to Order by Month Name in PostgreSQL or Oracle
• How to Get the Previous Month in SQL

Formatting Date and Time Values

PostgreSQL offers several formatting functions that make your reports easier to read. These include TO_CHAR(), TO_DATE(), and TO_TIMESTAMP().

TO_CHAR()

It converts a date/time value to a formatted string according to a specified format.

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

Check out the available date/time formats provided by PostgreSQL here.

TO_DATE()

It converts a string to a date value according to a specified format.

SELECT TO_DATE('2024-06-01', 'YYYY-MM-DD') AS date_value;

TO_TIMESTAMP()

It converts a string to a timestamp value according to a specified format.

SELECT TO_TIMESTAMP('2024-06-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS timestamp_value;

Check out these cookbooks to learn more:

• How to Format a Date in PostgreSQL
• How to Convert a String to a Date in PostgreSQL
• How to Convert a String to a Timestamp in PostgreSQL

Working with PostgreSQL Date and Time Functions

Date and time functions, as offered by PostgreSQL, are crucial tools in data analysis and reporting. They can help us understand data patterns over time, like sales trends or website traffic peaks. With these functions, we can group data by day, month, or year – making it easier to spot trends and make informed decisions. Plus, they help us format our reports, making them easier to read and understand.

As data is often stored in different databases or files, you can import your data in the CSV format into PostgreSQL for further processing.

If you need a handy reminder about SQL functions, check out our free Standard SQL Functions Cheat Sheet. You can download it, print it, and keep it handy as you work and learn.

If you want to solidify your knowledge of PostgreSQL, check out our comprehensive course SQL from A to Z in PostgreSQL. It offers hundreds of exercises to teach you everything you need to know to be a Postgres pro – including advanced concepts like window functions, recursive queries, and PostGIS. Happy learning!