Published at Mar 6, 2024
PostgreSQL’s date and time functions are key components in a developer's toolkit, offering the ability to manipulate and calculate dates and times efficiently. The open-source relational database offers an extensive suite of date/time functions that can significantly bolster your data manipulation capabilities.
PostgreSQL sets itself apart from other databases and programming languages with its comprehensive set of date/time functions. These include DATE_PART(), AGE(), EXTRACT(), NOW(), CURRENT_DATE, INTERVAL, and many more. They offer superior flexibility and precision in handling date and time data, making PostgreSQL a go-to choice for developers.
PostgreSQL's date/time functions provide precise results and flexibility in extracting or manipulating date and time data, ensuring accurate calculations and efficient data processing.
These functions also excel in handling time zones, a common challenge in programming. Functions like AT TIME ZONE can seamlessly convert timestamps between different time zones.
Functions like AGE() and DATE_PART() simplify complex date/time calculations, such as calculating age or extracting specific parts of a date/time value.
Now let's explore how these functions can be applied to real-world programming projects:
Project management tools: The AGE() function can be used to calculate the duration of a project or task.
Booking systems: The INTERVAL function can be used to add or subtract a specific period from a timestamp, helping in scheduling bookings.
Analytics applications: The DATE_PART() function can be used to extract specific parts of a timestamp for detailed temporal analysis.
The AGE() function calculates the interval between two timestamps. If only one timestamp is provided, it calculates the interval between the current time and the provided timestamp.
Example:
SELECT AGE(TIMESTAMP '2020-01-01'); -- Returns the duration from '2020-01-01' to the current date.
CURRENT_DATE returns the current date.
Example:
SELECT CURRENT_DATE; -- Returns the current date.
CURRENT_TIME returns the current time, including the time zone.
Example:
SELECT CURRENT_TIME; -- Returns the current time.
CURRENT_TIMESTAMP returns the current date and time with the time zone.
Example:
SELECT CURRENT_TIMESTAMP; -- Returns the current date and time.
The EXTRACT() function retrieves subfields, such as year, month, day, etc., from a date or time value. Example:
SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-31'); -- Returns 2020.
LOCALTIME returns the current time without the time zone.
Example:
SELECT LOCALTIME; -- Returns the current time.
LOCALTIMESTAMP returns the current date and time without the time zone.
Example:
SELECT LOCALTIMESTAMP; -- Returns the current date and time.
Similar to EXTRACT(), the DATE_PART() function retrieves subfields from a date or time value.
Example:
SELECT DATE_PART('year', TIMESTAMP '2020-12-31'); -- Returns 2020
The DATE_TRUNC() function truncates a timestamp to a specified precision.
Example:
SELECT DATE_TRUNC('year', TIMESTAMP '2020-12-31'); -- Returns '2020-01-01 00:00:00'
The NOW() function returns the current date and time.
Example:
SELECT NOW(); -- Returns the current date and time.
✨ Editor’s Note: Read How We Fixed Long-Running PostgreSQL now() Queries (and Made Them Lightning Fast).
The TO_DATE() function converts a string to a date.
Example:
SELECT TO_DATE('20201231', 'YYYYMMDD'); -- Returns '2020-12-31'
The TO_TIMESTAMP() function converts a string to a timestamp.
Example:
SELECT TO_TIMESTAMP('20201231 20:30:00', 'YYYYMMDD HH24:MI:SS'); -- Returns '2020-12-31 20:30:00'
Mastering PostgreSQL's date/time functions is an invaluable skill for any developer. Their precision, flexibility, time zone handling, and simplification of complex calculations make them indispensable for anyone working with PostgreSQL.
For further learning and a deeper understanding of these functions, you can explore the official PostgreSQL documentation.
Now that you’ve learned the basics of PostgreSQL functions, it’s time for a better alternative. Hyperfunctions are a series of SQL functions within TimescaleDB that make it easier to manipulate and analyze time-series data in PostgreSQL with fewer lines of code.
You can use hyperfunctions to calculate percentile approximations of data, compute time-weighted averages, downsample and smooth data, and perform faster COUNT DISTINCT queries using approximations. Moreover, hyperfunctions are simple to use: you call a hyperfunction using the same SQL syntax you know and love.
Learn more about hyperfunctions on our Docs page, or keep reading for more information on string functions.