Working with dates and timestamps

---

Working with Dates in SQL (Postgres)

Working with dates is a daily task in Data Engineering, particularly for partitioning, windowing, and trend analysis. SQL handles dates using three main types: DATE (YYYY-MM-DD), TIMESTAMP (Date + Time), and INTERVAL (a duration of time).

Here is the breakdown of the most vital functions in PostgreSQL.

---

Getting the Current Time

These functions are essential for "Last Updated" columns or filtering for recent events.

• CURRENT_DATE: Returns today's date.

• CURRENT_TIMESTAMP (or NOW()): Returns the date, time, and timezone.

• LOCALTIMESTAMP: Returns date and time without the timezone.

---

Extracting Parts of a Date

Often you need to group data by year, month, or even hour. For this, we use EXTRACT or DATE_PART.

SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year, -- SQL Standard
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DOW FROM order_date) AS day_of_week -- 0 is Sunday
FROM sales;

SELECT DATE_PART('year', TIMESTAMP '2026-01-26 08:30:00');
-- Result: 2026

Common Parts to Extract

Whether you use EXTRACT or DATE_PART, the available "parts" are the same. Here are the most common ones for data analysis:

Part	Description	Example Result
'year'	The 4-digit year	2026
'month'	Month number (1-12)	1
'day'	Day of the month (1-31)	26
'dow'	Day of Week (0 = Sunday, 6 = Saturday)	1 (Monday)
'doy'	Day of Year (1-366)	26
'epoch'	Seconds since 1970-01-01 (Unix time)	1737878400
'week'	ISO 8601 week number	05

---

The Power of DATE_TRUNC

This is arguably the most important function for Data Engineers. It "rounds" a timestamp down to the nearest interval (day, month, hour, etc.). It is much more efficient for grouping than extracting strings.

-- "Rounds" 2024-05-15 14:30:05 down to 2024-05-01 00:00:00
SELECT 
    DATE_TRUNC('month', pickup_datetime) AS month_bucket,
    COUNT(*) 
FROM taxi_trips
GROUP BY 1;

---

Intervals and Date Math

SQL allows you to add or subtract time using the INTERVAL keyword. This is how you create "lookback" windows.

-- Subtract 30 days from now
SELECT * FROM logs 
WHERE created_at > NOW() - INTERVAL '30 days';

-- Add 1 year and 2 months to a date
SELECT order_date + INTERVAL '1 year 2 months' FROM orders;

Calculating the difference between two dates:

Simply subtracting two dates returns an integer (number of days), but subtracting two timestamps returns an INTERVAL.

SELECT '2024-01-10'::DATE - '2024-01-01'::DATE; -- Result: 9 (integer)

equivalent syntax to the above 'string'::DATE syntax

In PostgreSQL, the :: operator is a shorthand for the standard SQL CAST function.

A. The Standard CAST Function (Portable) This is the official SQL standard and works in almost every database.

SELECT CAST('2024-01-10' AS DATE);

B. The Type Literal (Prefix) This is also a SQL standard. You place the type before the string literal.

SELECT DATE '2024-01-10';

C. The TO_DATE Function Used when your string isn't in the standard YYYY-MM-DD format and you need to tell Postgres how to read it.

SELECT TO_DATE('10-01-2024', 'DD-MM-YYYY');

---

Age and Formatting

• AGE(timestamp): Calculates the difference between now and a date (returns an interval).

• TO_CHAR(timestamp, format): Converts a date into a specific string format (great for reporting).

SELECT 
    TO_CHAR(NOW(), 'Day, DD Month YYYY') AS readable_date, -- "Monday, 26 January 2026"
    AGE(birth_date) AS user_age
FROM users;

---

Formatting Table for TO_CHAR

Pattern	Description	Result Example
YYYY	4-digit year	2026
MM	Month number	01
Month	Full month name	January
DD	Day of month	26
Day	Full day name	Monday
HH24	Hour (0-23)	14

---

Summary

• Use DATE_TRUNC for bucketing data into time periods (Day, Month, Year).

• Use INTERVAL for any date arithmetic (adding/subtracting time).

• Use EXTRACT when you need a specific integer (like "Which hour of the day is busiest?").

---

Working with timestamps

In PostgreSQL, timestamps are more complex than simple dates because they involve precision and, most importantly, timezones. As a Data Engineer, handling timezones correctly is the difference between a reliable global system and a debugging nightmare.

---

The Two Main Timestamp Types

PostgreSQL offers two distinct ways to store time:

• TIMESTAMP (without time zone): Stores exactly what you give it. If you insert 2026-01-26 08:00:00, it stores that regardless of where the server is located.

  • Best for: Local events (e.g., "The store opens at 8 AM local time" regardless of the city).

• TIMESTAMPTZ (with time zone): This is the industry standard for data pipelines. It converts any input to UTC and stores it. When you query it, Postgres converts it back to your current session's timezone.

  • Best for: Transaction logs, event tracking, and global data syncing.

Pro-Tip: Always use TIMESTAMPTZ. It prevents the common "shifting data" bug where records seem to move by several hours when viewed from different locations.

---

Converting and Casting Timestamps

Since we are focusing on Postgres, you have several ways to turn strings into timestamps:

-- Shorthand casting
SELECT '2026-01-26 08:30:00'::TIMESTAMPTZ;

-- Standard CAST
SELECT CAST('2026-01-26 08:30:00' AS TIMESTAMPTZ);

-- TO_TIMESTAMP (Useful for custom formats or Unix Epochs)
SELECT TO_TIMESTAMP('2026/01/26 08:30', 'YYYY/MM/DD HH24:MI');
SELECT TO_TIMESTAMP(1737878400); -- Converts Unix seconds to Timestamp

---

Working with Timezones (AT TIME ZONE)

The AT TIME ZONE operator is your primary tool for shifting timestamps between zones. It behaves differently depending on the input:

1. TIMESTAMPTZ $\to$ TIMESTAMP: Converts the UTC value to the local time of the specified zone.

2. TIMESTAMP $\to$ TIMESTAMPTZ: Takes a "dumb" timestamp and tells Postgres, "Treat this as if it were in this specific zone."

-- What time is it in New York for this UTC event?
SELECT NOW() AT TIME ZONE 'America/New_York';

-- Treat a local string as if it happened in Tokyo, then store as UTC
SELECT '2026-01-26 08:00:00'::TIMESTAMP AT TIME ZONE 'Asia/Tokyo';

---

Precision and Intervals

Timestamps in Postgres can store up to 6 decimal places (microseconds). You can limit this by defining the type as TIMESTAMP(0) to remove the fractional seconds.

Timestamp Math:

Subtracting two timestamps produces an INTERVAL type.

SELECT 
    '2026-01-26 10:00:00'::TIMESTAMP - '2026-01-26 08:30:00'::TIMESTAMP 
    AS duration;
-- Result: 01:30:00 (an interval)

---

Useful Timestamp Functions

Function	Output	Purpose
NOW()	timestamptz	Current date/time (start of transaction).
clock_timestamp()	timestamptz	Current actual time (changes during the query).
make_timestamp(y, m, d, h, m, s)	timestamp	Build a timestamp from individual integers.
age(ts1, ts2)	interval	Detailed breakdown of time between two timestamps.

---

Summary

• Rule #1: Store everything in TIMESTAMPTZ.

• Rule #2: Use UTC as your database's default timezone (SET timezone TO 'UTC';).

• Rounding: Use DATE_TRUNC('hour', ts) to bucket timestamps for time-series analysis.

• Comparison: Remember that TIMESTAMP '2026-01-01' is technically the same as 2026-01-01 00:00:00.000000.

---