Time Features for ML

Medium10 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have a sales table with a timestamp column.

Table: sales

id	transaction_date
1	2025-03-01 09:30:00
2	2025-03-03 18:00:00
3	2025-03-03 21:45:00

Task

Build features for a model from transaction_date:

year, month, day, day of week and an is_weekend flag
the hour of day as a cyclical feature (sine and cosine)
the time since the previous transaction

Expected output (some columns)

id	day_of_week	is_weekend	time_since_prev
1	6	1	NULL
2	1	0	2 days 08:30:00
3	1	0	03:45:00

Work out your query on your own, then open Answers & Optimization to compare.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

Calendar parts (EXTRACT) give simple numeric features.
Hour, day of week and month are cyclical: hour 23 is next to hour 0. Encode them as sin and cos so the model sees that.
Gaps between events (LAG) are strong features for churn, fraud and demand models.

1Basic

EXTRACT every calendar part

1SELECT id, transaction_date,2       EXTRACT(YEAR  FROM transaction_date) AS year,3       EXTRACT(MONTH FROM transaction_date) AS month,4       EXTRACT(DAY   FROM transaction_date) AS day,5       EXTRACT(DOW   FROM transaction_date) AS day_of_week,6       CASE WHEN EXTRACT(DOW FROM transaction_date) IN (0, 6) THEN 1 ELSE 0 END AS is_weekend,7       EXTRACT(HOUR  FROM transaction_date) AS hour8FROM sales;

PostgreSQL: DOW runs 0 (Sunday) to 6
MySQL uses DAYOFWEEK(), where 1 is Sunday

2Optimized

One pass: cyclical encoding and LAG

1WITH t AS (2  SELECT id, transaction_date,3         EXTRACT(HOUR FROM transaction_date) + EXTRACT(MINUTE FROM transaction_date) / 60.0 AS hour_of_day4  FROM sales5)6SELECT id, transaction_date,7       EXTRACT(DOW FROM transaction_date)                  AS day_of_week,8       SIN(2 * PI() * hour_of_day / 24)                    AS tod_sin,9       COS(2 * PI() * hour_of_day / 24)                    AS tod_cos,10       transaction_date11         - LAG(transaction_date) OVER (ORDER BY transaction_date) AS time_since_prev12FROM t13ORDER BY transaction_date;

Why sin and cos: a raw hour number says 23 and 0 are far apart
Per-customer gaps: add PARTITION BY customer_id inside OVER
Index the timestamp column so the ORDER BY needs no sort

Spotted a mistake in these answers?

Report a problem