DATE/TIME Functions and Operators
With SQL standard compliance and strong extensions, PostgreSQL manages temporal information with a broad set of data types, functions, and operators. The main data types are date, time, timestamp, and timezone-aware timestamptz. To handle daylight saving time correctly, timestamptz values are saved internally in Coordinated Universal Time (UTC) and immediately converted to the session’s local time upon retrieval. Date and time arithmetic is simply handled using + and – operators and the interval type, which represents duration.
PostgreSQL’s now() and CURRENT_TIMESTAMP functions return the transaction’s start time for consistency, but clock_timestamp() returns the wall-clock time that changes even within a statement. EXTRACT() and date_part() can obtain subfields like year, month, or the Unix epoch, while date_trunc() truncates results to an hour or day, making it useful for time-based aggregation.
Core Date/Time Data Types
Understand PostgreSQL’s basic data types for temporal information before learning about functions and operators.
Timestamp (or timestamp without time zone): Instead of time zones, this kind records date and time. Changing the server’s time zone won’t modify the displayed value.
Timestamptz (or timestamp with time zone): Time zones affect this date/time data. Note that the table does not store the time zone. Coordinated Universal Time (UTC) is used internally to store all timestamptz values instead. Upon retrieval, a value is automatically transformed from UTC to the local time of the TimeZone setting of the current session. It is therefore the best option for apps that deal with customers in different time zones.
Date: Date, one of PostgreSQL’s fundamental temporal data types, stores merely a calendar date with the year, month, and day. It does not understand time zones or time-of-day components like hours, minutes, or seconds, unlike timestamp.
Time (or time without time zone): This kind lacks a date and time zone information and just saves the time of day. Although there is a time with time zone (timetz), its use is usually discouraged because of the possible ambiguity.
Interval: This strong type denotes a term like “one month,” “two years,” or “three hours forty minutes.” Completing date and time arithmetic requires it.
Code Example:
-- Drop and recreate table
DROP TABLE IF EXISTS temporal_demo;
CREATE TABLE temporal_demo(
ts TIMESTAMP,
tstz TIMESTAMPTZ,
d DATE,
t TIME,
i INTERVAL
);
-- Insert one row
INSERT INTO temporal_demo VALUES(
'2025-09-18 14:30',
'2025-09-18 14:30+05:30',
'2025-09-18',
'14:30',
'2 days 3 hours'
);
-- View stored values
SELECT * FROM temporal_demo;
-- Time zone effect on timestamptz
SET TIMEZONE = 'UTC';
SELECT tstz AS utc_time FROM temporal_demo;
SET TIMEZONE = 'America/New_York';
SELECT tstz AS ny_time FROM temporal_demo;
-- Interval arithmetic
SELECT d + i AS date_plus_i, ts + i AS ts_plus_i FROM temporal_demo;Output:
DROP TABLE
CREATE TABLE
INSERT 0 1
ts | tstz | d | t | i
---------------------+------------------------+------------+----------+-----------------
2025-09-18 14:30:00 | 2025-09-18 09:00:00+00 | 2025-09-18 | 14:30:00 | 2 days 03:00:00
(1 row)
SET
utc_time
------------------------
2025-09-18 09:00:00+00
(1 row)
SET
ny_time
------------------------
2025-09-18 05:00:00-04
(1 row)
date_plus_i | ts_plus_i
---------------------+---------------------
2025-09-20 03:00:00 | 2025-09-20 17:30:00
(1 row)Key Date/Time Functions and Operators
A comprehensive set of operators and functions for working with various data types is provided by PostgreSQL.
Retrieving Current Time: PostgreSQL has a number of functions for retrieving the current time and date, each having a unique transaction scope behavior.
- Now(), TRANSACTION_TIMESTAMP(), and CURRENT_TIMESTAMP the current transaction’s start time is returned by these conventional equivalents. They maintain a constant concept of “now” by not changing their values during the same transaction.
- The start time of the current statement is returned by statement_timestamp().
- Even inside a single SQL statement, the value of clock_timestamp(), which returns the real current time, are subject to change.
- The present time and date provide the time and date as of right now.
Date/Time Arithmetic: It is frequently necessary to do arithmetic procedures. PostgreSQL’s interval operators simplify this.
- The + and – operators add or subtract intervals from dates, times, and timestamps. Subtracting two date or timestamp values gives an interval (or integer representing days for date-date).
- The “symbolic” output in years, months, and days given by age() by subtracting two timestamps is sometimes easier to grasp than an interval. ‘2001-04-10’, ‘1957-06-13’ yields 43 years, 9 months, and 27 days.
Formatting and Converting Data: This essential formatting technique converts date/time data to text using a template. Months, days, hours, minutes, seconds, and four-digit years are MM, DD, HH24, MI, SS, and YYYY. SELECT to_char(‘2016-08-12 16:40:32’::timestamp, ‘DD’) shows “12 Aug 2016 04:40:32 PM” Monday HH:MI:SSPM Templates format strings into timestamps and dates in to_timestamp() and to_date().
Extracting and Truncating: From date/time variables, Date_part() and EXTRACT() return year, month, day, hour, and minute. Epoch specifies 1970-01-01 00:00:00 UTC seconds, which is useful. Truncate intervals to hours, days, or months with Date_trunc(). Group-by-clause data accumulate. The default date_trunc(‘hour’, ‘2001-02-16 20:38:40’) is 20:00:00.
Time Zone Conversions: The SQL strong AT TIME ZONE construct changes time zones.
- Interpreting a timestamp’s time zone yields UTC. Time zone ‘America/Denver’ matches UTC and Denver input time.
- It returns the local time without time zone in a zone from a UTC-based timestamp with time zone value. At Time Zone ‘America/Denver’, ‘2001-02-16 20:38:40-05’ returns 18:38:40.
Generating Series: The versatile set-returning, or table, generate_series() function in PostgreSQL constructs a sequence of values directly within a SQL query. This useful feature lets writers replicate a SQL for loop by generating integers or timestamps with start, stop, and step intervals.
Developers may manage temporal data with PostgreSQL date/time types, operators, and functions. Time and date management applications benefit from its broad conversion and manipulation tools and clear separation of timezone-aware and non-aware types.