PostgreSQL Date and Time

Overview

PostgreSQL provides rich date and time data types and functions, supporting timezone handling, interval calculations, and other advanced features.

Date and Time Data Types

Type	Storage Size	Description	Range
DATE	4 bytes	Date	4713 BC to 5874897 AD
TIME	8 bytes	Time (without timezone)	00:00:00 to 24:00:00
TIMETZ	12 bytes	Time (with timezone)	00:00:00+1559 to 24:00:00-1559
TIMESTAMP	8 bytes	Date and time (without timezone)	4713 BC to 294276 AD
TIMESTAMPTZ	8 bytes	Date and time (with timezone)	4713 BC to 294276 AD
INTERVAL	16 bytes	Time interval	-178000000 to 178000000 years

Getting Current Date and Time

-- Current date
SELECT CURRENT_DATE;              -- 2024-01-15
SELECT NOW()::DATE;               -- 2024-01-15

-- Current time
SELECT CURRENT_TIME;              -- 14:30:00.123456+08
SELECT LOCALTIME;                 -- 14:30:00.123456 (without timezone)

-- Current timestamp
SELECT CURRENT_TIMESTAMP;         -- 2024-01-15 14:30:00.123456+08
SELECT NOW();                     -- 2024-01-15 14:30:00.123456+08
SELECT LOCALTIMESTAMP;            -- 2024-01-15 14:30:00.123456 (without timezone)

-- Transaction start time
SELECT transaction_timestamp();
SELECT statement_timestamp();
SELECT clock_timestamp();         -- Actual current time (may differ on each call)

Date and Time Input Formats

-- DATE formats
SELECT '2024-01-15'::DATE;
SELECT 'January 15, 2024'::DATE;
SELECT '01/15/2024'::DATE;
SELECT '20240115'::DATE;

-- TIME formats
SELECT '14:30:00'::TIME;
SELECT '2:30 PM'::TIME;
SELECT '143000'::TIME;

-- TIMESTAMP formats
SELECT '2024-01-15 14:30:00'::TIMESTAMP;
SELECT '2024-01-15T14:30:00'::TIMESTAMP;
SELECT 'January 15, 2024 2:30 PM'::TIMESTAMP;

-- With timezone
SELECT '2024-01-15 14:30:00+08'::TIMESTAMPTZ;
SELECT '2024-01-15 14:30:00 Asia/Shanghai'::TIMESTAMPTZ;

EXTRACT Function

Extract parts of a date or time:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-01-15 14:30:00');    -- 2024
SELECT EXTRACT(MONTH FROM TIMESTAMP '2024-01-15 14:30:00');   -- 1
SELECT EXTRACT(DAY FROM TIMESTAMP '2024-01-15 14:30:00');     -- 15
SELECT EXTRACT(HOUR FROM TIMESTAMP '2024-01-15 14:30:00');    -- 14
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2024-01-15 14:30:00');  -- 30
SELECT EXTRACT(SECOND FROM TIMESTAMP '2024-01-15 14:30:00');  -- 0

-- Other extraction options
SELECT EXTRACT(DOW FROM DATE '2024-01-15');      -- Day of week (0=Sunday)
SELECT EXTRACT(DOY FROM DATE '2024-01-15');      -- Day of year
SELECT EXTRACT(WEEK FROM DATE '2024-01-15');     -- Week number
SELECT EXTRACT(QUARTER FROM DATE '2024-01-15');  -- Quarter
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-15 14:30:00');  -- Unix timestamp

DATE_PART Function

Similar to EXTRACT:

SELECT DATE_PART('year', TIMESTAMP '2024-01-15 14:30:00');   -- 2024
SELECT DATE_PART('month', TIMESTAMP '2024-01-15 14:30:00');  -- 1
SELECT DATE_PART('day', TIMESTAMP '2024-01-15 14:30:00');    -- 15

Date and Time Arithmetic

Using INTERVAL

-- Add/subtract days
SELECT DATE '2024-01-15' + INTERVAL '7 days';       -- 2024-01-22
SELECT DATE '2024-01-15' - INTERVAL '7 days';       -- 2024-01-08

-- Add/subtract months
SELECT DATE '2024-01-15' + INTERVAL '1 month';      -- 2024-02-15
SELECT DATE '2024-01-15' + INTERVAL '3 months';     -- 2024-04-15

-- Add/subtract years
SELECT DATE '2024-01-15' + INTERVAL '1 year';       -- 2025-01-15

-- Add/subtract time
SELECT TIMESTAMP '2024-01-15 14:30:00' + INTERVAL '2 hours';    -- 16:30:00
SELECT TIMESTAMP '2024-01-15 14:30:00' + INTERVAL '30 minutes'; -- 15:00:00

-- Compound interval
SELECT DATE '2024-01-15' + INTERVAL '1 year 2 months 3 days';

Date Difference

-- Difference between two dates (in days)
SELECT DATE '2024-01-20' - DATE '2024-01-15';  -- 5

-- Difference between two timestamps (INTERVAL)
SELECT TIMESTAMP '2024-01-20 10:00' - TIMESTAMP '2024-01-15 08:00';
-- 5 days 02:00:00

-- Calculate age
SELECT AGE(TIMESTAMP '2024-01-15', TIMESTAMP '2000-06-20');
-- 23 years 6 mons 25 days

Date and Time Formatting

TO_CHAR Function

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');           -- 2024-01-15
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 2024-01-15 14:30:00
SELECT TO_CHAR(NOW(), 'Day, Month DD, YYYY');   -- Monday, January 15, 2024
SELECT TO_CHAR(NOW(), 'HH:MI:SS AM');           -- 02:30:00 PM

Common Format Patterns

Pattern	Description	Example
YYYY	4-digit year	2024
MM	Month (01-12)	01
DD	Day (01-31)	15
HH24	24-hour clock	14
HH12/HH	12-hour clock	02
MI	Minutes	30
SS	Seconds	00
AM/PM	AM/PM indicator	PM
Day	Full day name	Monday
Mon	Abbreviated month	Jan

String to Date

SELECT TO_DATE('15-01-2024', 'DD-MM-YYYY');
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

Date Truncation

-- Truncate to day
SELECT DATE_TRUNC('day', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-15 00:00:00

-- Truncate to month
SELECT DATE_TRUNC('month', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-01 00:00:00

-- Truncate to year
SELECT DATE_TRUNC('year', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-01 00:00:00

-- Truncate to hour
SELECT DATE_TRUNC('hour', TIMESTAMP '2024-01-15 14:30:45');
-- 2024-01-15 14:00:00

Timezone Handling

-- Set timezone
SET TIME ZONE 'Asia/Shanghai';
SET TIME ZONE 'UTC';

-- View current timezone
SHOW TIME ZONE;

-- Timezone conversion
SELECT TIMESTAMP '2024-01-15 14:30:00' AT TIME ZONE 'Asia/Shanghai';
SELECT TIMESTAMP WITH TIME ZONE '2024-01-15 14:30:00+08'
    AT TIME ZONE 'UTC';

-- Create timestamp with timezone
SELECT '2024-01-15 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';

Practical Examples

Query Specific Date Range

-- Today's orders
SELECT * FROM orders
WHERE created_at::DATE = CURRENT_DATE;

-- This month's orders
SELECT * FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- Last 7 days
SELECT * FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

Group Statistics by Time

-- Monthly statistics
SELECT
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Hourly statistics
SELECT
    EXTRACT(HOUR FROM created_at) as hour,
    COUNT(*) as order_count
FROM orders
GROUP BY EXTRACT(HOUR FROM created_at)
ORDER BY hour;

Calculate Business Days

-- Count weekdays excluding weekends
SELECT COUNT(*)
FROM generate_series(
    DATE '2024-01-01',
    DATE '2024-01-31',
    INTERVAL '1 day'
) AS day
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6);  -- Exclude Sunday(0) and Saturday(6)

Summary

Date/Time:

• DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
• EXTRACT for parts
• Arithmetic operations
• TO_CHAR for formatting
• Time zone support