Work with dates and time

TutorialApplies to: Unified engine deployments

Use fixed dates so every run has a clear expected result.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Extract a year

Which year contains each event?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT id, DATE_PART('year', day) AS year FROM events ORDER BY id;

Expected result

id	year
1	2024
2	2024
3	2024

DATE_PART reads a calendar field. Change one thing: Use month instead of year. Expect 1, 1, and 2.

2. Extract a day

On which day of the month did each event occur?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT id, DATE_PART('day', day) AS day_number FROM events ORDER BY id;

Expected result

id	day_number
1	15
2	20
3	3

The day field is the day within the month. Change one thing: Use month instead. Expect 1, 1, and 2.

3. Filter a date range

Which events occurred in January?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT id FROM events WHERE day >= DATE '2024-01-01' AND day < DATE '2024-02-01' ORDER BY id;

Expected result

id
1
2

A half-open range includes the start and excludes the next period. Change one thing: Use February boundaries. Only ID 3 appears.

4. Group by month

What are monthly sales?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT DATE_PART('month', day) AS month, SUM(amount) AS sales FROM events GROUP BY DATE_PART('month', day) ORDER BY month;

Expected result

month	sales
1	30
2	30

This dataset contains one year. Include year when data spans several years. Change one thing: Use COUNT(*) instead of SUM(amount). Expect January 2 and February 1.

5. Find month boundaries

What is the first day of each event month?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT id, CAST(DATE_TRUNC('month', CAST(day AS TIMESTAMP_NTZ)) AS DATE) AS month_start FROM events ORDER BY id;

Expected result

id	month_start
1	2024-01-01
2	2024-01-01
3	2024-02-01

TIMESTAMP_NTZ gives this truncation a timestamp without time-zone conversion. Change one thing: Use year instead of month. All rows return 2024-01-01.

6. Add an interval

What is the following calendar day?

WITH events(id, day, amount) AS (VALUES (1, DATE '2024-01-15', 10), (2, DATE '2024-01-20', 20), (3, DATE '2024-02-03', 30))
SELECT id, CAST(day + INTERVAL '1' DAY AS DATE) AS next_day FROM events ORDER BY id;

Expected result

id	next_day
1	2024-01-16
2	2024-01-21
3	2024-02-04

An interval expresses a calendar offset. Change one thing: Change 1 to 2. Expect January 17, January 22, and February 5.

Continue

Continue to Rank and compare rows, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Work with dates and time

Before you start

1. Extract a year

2. Extract a day

3. Filter a date range

4. Group by month

5. Find month boundaries

6. Add an interval

Continue

​Before you start

​1. Extract a year

​2. Extract a day

​3. Filter a date range

​4. Group by month

​5. Find month boundaries

​6. Add an interval

​Continue

Before you start

1. Extract a year

2. Extract a day

3. Filter a date range

4. Group by month

5. Find month boundaries

6. Add an interval

Continue