---
title: "PostgreSQL Date and Time Functions"
description: "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."
section: "Postgres basics"
---
> **TimescaleDB is now Tiger Data.**
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.
> [Learn how to create, call, list, and edit Postgres functions](https://www.timescale.com/learn/postgres-cheat-sheet/functions).
##
PostgreSQL Date and Time Functions
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.
##
Advantages of Using PostgreSQL Date and Time Functions
### Precision and flexibility
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.
### Time zone handling
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.
### Simplified complex calculations
Functions like `AGE()` and `DATE_PART()` simplify complex date/time calculations, such as calculating age or extracting specific parts of a date/time value.
## Practical Use Cases of PostgreSQL Date and Time Functions
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.
## List of PostgreSQL Date and Time Functions
### **AGE(timestamp1, [timestamp2])**
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**
`CURRENT_DATE` returns the current date.
Example:
`SELECT CURRENT_DATE; -- Returns the current date.`
### **CURRENT_TIME**
`CURRENT_TIME` returns the current time, including the time zone.
Example:
`SELECT CURRENT_TIME; -- Returns the current time.`
### **CURRENT_TIMESTAMP**
`CURRENT_TIMESTAMP` returns the current date and time with the time zone.
Example:
`SELECT CURRENT_TIMESTAMP; -- Returns the current date and time.`
### **EXTRACT(field FROM source)**
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**
`LOCALTIME` returns the current time without the time zone.
Example:
`SELECT LOCALTIME; -- Returns the current time.`
### **LOCALTIMESTAMP**
`LOCALTIMESTAMP `returns the current date and time without the time zone.
Example:
`SELECT LOCALTIMESTAMP; -- Returns the current date and time.`
### **DATE_PART(field, source)**
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`
### **DATE_TRUNC(field, source)**
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'`
### **NOW()**
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).](https://www.timescale.com/blog/how-we-fixed-long-running-postgresql-now-queries/)
### **TO_DATE(text, format)**
The `TO_DATE() `function converts a string to a date.
Example:
`SELECT TO_DATE('20201231', 'YYYYMMDD'); -- Returns '2020-12-31'`
### **TO_TIMESTAMP(text, format)**
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'`
## A Summary on Date and Time Functions
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](https://www.postgresql.org/docs/current/functions-datetime.html).
## Use Timescale Functions for Hyper Speed and Ease
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](https://docs.timescale.com/api/latest/hyperfunctions/), or keep reading for more information on [string functions](https://www.timescale.com/learn/postgres-string-agg-function).