Date and Time Functions

Time is one of the most complex data types to handle. Between leap years, time zones, and varying date formats, you need specialized functions to perform even basic arithmetic on dates.

Definition: Temporal Data Types

Most databases support several temporal types:

• DATE: Just the day, month, and year (e.g., 2023-12-25).
• TIME: Just the time of day (e.g., 14:30:05).
• TIMESTAMP / DATETIME: Both date and time.
• INTERVAL: A duration of time (e.g., 3 days, 5 hours).

Context: The Current Moment

Every database has a way to get the current date and time. This is useful for calculating “age” or filtering for recent events.

• Postgres: NOW(), CURRENT_DATE
• SQL Server: GETDATE(), SYSDATETIME()
• MySQL: NOW(), CURDATE()

Detailed Explanation: Key Operations

1. Extraction (Getting parts of a date)

Getting the year, month, or day from a timestamp.

Date Extraction

SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-12-25 10:00:00'); -- Result: 2023

2. Date Arithmetic (Adding/Subtracting)

Calculating a future or past date.

• Postgres: CURRENT_DATE + INTERVAL '1 month'
• SQL Server: DATEADD(month, 1, GETDATE())

3. Calculating Differences

Finding the time between two events.

• Postgres: date1 - date2 (returns an interval or number of days).
• SQL Server: DATEDIFF(day, date1, date2)

Example: Shipping Analysis

We have an Orders table:

OrderID	OrderDate	ShippedDate
101	2023-01-01	2023-01-03
102	2023-01-05	2023-01-15
103	2023-02-10	NULL

Task: Calculate shipping time in days and identify the month of the order.

Date Calculations

SELECT
  OrderID,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  ShippedDate - OrderDate AS DaysToShip
FROM Orders
WHERE ShippedDate IS NOT NULL;

Output

OrderID	OrderMonth	DaysToShip
101	1	2
102	1	10

Under the Hood: Unix Epoch and Storage

How does a database “know” that January 31st + 1 day is February 1st?

Most databases do not store dates as strings like “2023-01-01”. Instead, they store them as numbers.

• Many systems use the Unix Epoch: the number of seconds (or milliseconds) that have passed since January 1, 1970, 00:00:00 UTC.
• Others store dates as a floating-point number where the integer part is the number of days since a “base date” (e.g., 1900-01-01) and the decimal part represents the time of day.

Time Zones (The Silent Killer)

A common mistake is mixing TIMESTAMP (no time zone) with TIMESTAMP WITH TIME ZONE.

• If your server is in New York (EST) and your user is in London (GMT), a “midnight” order might be recorded on two different days depending on how your database handles offsets.

Tip

Always store your data in UTC (Coordinated Universal Time) and convert to the user’s local time zone in the application layer or the final SELECT statement.