SQL Dates and Time

Working with dates and timestamps is genuinely its own skill — this series covers reading the current moment, pulling parts out of a date, doing arithmetic, formatting for display, and filtering by relative time windows.

What & Why

Almost every real dataset has a date or timestamp column somewhere — a user signup, a campaign start date, or a lead creation time. This series covers reading the database clock, extracting calendar fields, truncating timestamps to reporting boundaries, date arithmetic, formatting, and relative windows such as the last 30 days.

See How It Works

BUSINESS QUESTION

Growth wants recent signup moments together with their calendar reporting date.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

SELECT
  id AS user_id,
  created_at,
  created_at::date AS signup_date
FROM growth.users
ORDER BY created_at DESC
LIMIT 20;

RESULT — exact output from the displayed Queryflo rows

user_id	created_at	signup_date
104	2024-04-01 13:20:00+00	2024-04-01
103	2024-03-12 16:35:00+00	2024-03-12
102	2024-02-10 11:05:00+00	2024-02-10
101	2024-01-15 09:10:00+00	2024-01-15

Casting preserves the original timestamp while deriving its calendar date.

Now You Try

Practice this concept

Growth wants recent signup moments together with their calendar date.

Available schema

growth

Prefix tables with growth.table_name.

user_idcreated_atsignup_date

growth.users

Column	Type
id	integer
created_at	timestamp with time zone
country	text
channel	text
plan	text
activated_at	timestamp with time zone
churned_at	timestamp with time zone
legacy_user_code	text

query.sql

Intermediate business practice