> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Work with dates and time

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/rank-and-compare.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.