SQL: Tame your timestamps

Working with dates, times, and timestamps in SQL often becomes frustrating when you need to extract specific time components for filtering, grouping, or analysis in general. While SQL provides numerous datetime functions, remembering all of them, along with their exact syntax, can be challenging.

We suggest a simple solution.

Create a dedicated view that breaks down timestamps into clear, reusable columns. This approach lets you work with pre-formatted date components without repeating complex datetime functions in every query.

Why this is useful

Initially, applying datetime functions directly in your queries might seem sufficient. However, as your queries grow more complex, functions like YEAR(dtm), MONTH(dtm), or STRFTIME(dtm, '%a') accumulate, making your SQL harder to read, maintain, and debug.

A dedicated timestamp view addresses these challenges by:

• Centralizing all datetime related logic in one reusable component,
• ensuring consistency across different queries and reports,
• simplifying query writing and maintenance.

Sample data

We'll use DuckDB for this demonstration. The dataset was generated using the Synthetic Data Generator.

CREATE TABLE customer AS
SELECT 
    company_01 AS company, 
    date_time_this_year_02 AS dtm
FROM 'https://www.lovelydata.cz/media/files/datetime_data.csv';

-- Select first 5 rows
FROM customer
LIMIT 5;

|              company               |         dtm         |
|------------------------------------|---------------------|
| Bates, Jensen and Kramer           | 2025-08-30 13:29:46 |
| Hernandez, Townsend and Montgomery | 2025-06-05 20:05:39 |
| Arroyo and Sons                    | 2025-05-29 15:22:54 |
| Munoz Group                        | 2025-07-16 04:56:20 |
| Martin Inc                         | 2025-01-17 10:25:24 |

Understanding date and time functions

SQL provides extensive functionality for extracting components from datetime values. The exact functions vary by database system, but the concepts remain similar.

Date part extraction functions

Most databases offer functions to extract specific components from datetime values:

SELECT 
    dtm,
    YEAR(dtm)   AS year,
    MONTH(dtm)  AS month,
    DAY(dtm)    AS day,
    HOUR(dtm)   AS hour,
    MINUTE(dtm) AS minute,
    SECOND(dtm) AS second,
    QUARTER(dtm) AS quarter,
    WEEK(dtm)   AS week
FROM customer
LIMIT 5;

|         dtm         | year | month | day | hour | minute | second | quarter | week |
|---------------------|-----:|------:|----:|-----:|-------:|-------:|--------:|-----:|
| 2025-08-30 13:29:46 | 2025 | 8     | 30  | 13   | 29     | 46     | 3       | 35   |
| 2025-06-05 20:05:39 | 2025 | 6     | 5   | 20   | 5      | 39     | 2       | 23   |
| 2025-05-29 15:22:54 | 2025 | 5     | 29  | 15   | 22     | 54     | 2       | 22   |
| 2025-07-16 04:56:20 | 2025 | 7     | 16  | 4    | 56     | 20     | 3       | 29   |
| 2025-01-17 10:25:24 | 2025 | 1     | 17  | 10   | 25     | 24     | 1       | 3    |

Date formatting functions

For more flexible formatting, string-based functions like STRFTIME() (in DuckDB and SQLite) or TO_CHAR() (in PostgreSQL and Oracle) let you customize datetime output:

SELECT
    dtm,
    STRFTIME(dtm, '%a') AS weekday_short,
    STRFTIME(dtm, '%A') AS weekday_full,
    STRFTIME(dtm, '%b') AS monthname_short,
    STRFTIME(dtm, '%B') AS monthname_full,
    STRFTIME(dtm, '%p') AS am_pm,
    STRFTIME(dtm, '%Y-%m') AS year_month
FROM customer
LIMIT 5;

|         dtm         | weekday_short | weekday_full | monthname_short | monthname_full | am_pm | year_month |
|---------------------|---------------|--------------|-----------------|----------------|-------|------------|
| 2025-08-30 13:29:46 | Sat           | Saturday     | Aug             | August         | PM    | 2025-08    |
| 2025-06-05 20:05:39 | Thu           | Thursday     | Jun             | June           | PM    | 2025-06    |
| 2025-05-29 15:22:54 | Thu           | Thursday     | May             | May            | PM    | 2025-05    |
| 2025-07-16 04:56:20 | Wed           | Wednesday    | Jul             | July           | AM    | 2025-07    |
| 2025-01-17 10:25:24 | Fri           | Friday       | Jan             | January        | AM    | 2025-01    |

Building the comprehensive timestamp view

Let's create a view that includes all the useful datetime components in one place:

CREATE VIEW v_customer AS
SELECT 
    company, 
    dtm AS original_timestamp,
    
    -- Basic date components
    YEAR(dtm) AS "year",
    MONTH(dtm) AS "month",
    DAY(dtm) AS "day",
    HOUR(dtm) AS "hour",
    MINUTE(dtm) AS "minute",
    SECOND(dtm) AS "second",
    
    -- Time period identifiers
    QUARTER(dtm) AS "quarter",
    WEEK(dtm) AS "week",
    ISODOW(dtm) AS "isoweekday",  -- Monday=1, Sunday=7
    
    -- Formatted date parts
    STRFTIME(dtm, '%a') AS "weekday_short",   
    STRFTIME(dtm, '%A') AS "weekday_full",
    STRFTIME(dtm, '%b') AS "monthname_short", 
    STRFTIME(dtm, '%B') AS "monthname_full",
    STRFTIME(dtm, '%p') AS "am_pm",
    
    -- Combined date formats
    STRFTIME(dtm, '%Y-%m') AS "year_month",
    STRFTIME(dtm, '%Y-%m-%d') AS "date_iso",
    
    -- Business time classifications
    CASE 
        WHEN HOUR(dtm) BETWEEN 9 AND 17 THEN 'Business Hours'
        ELSE 'After Hours'
    END AS "time_category"
FROM customer;

-- Select first 5 rows
FROM v_customer
LIMIT 5;

|              company               | original_timestamp  | year | month | day | hour | minute | second | quarter | week | isoweekday | weekday_short | weekday_full | monthname_short | monthname_full | am_pm | year_month |  date_iso  | time_category  |
|------------------------------------|---------------------|-----:|------:|----:|-----:|-------:|-------:|--------:|-----:|-----------:|---------------|--------------|-----------------|----------------|-------|------------|------------|----------------|
| Bates, Jensen and Kramer           | 2025-08-30 13:29:46 | 2025 | 8     | 30  | 13   | 29     | 46     | 3       | 35   | 6          | Sat           | Saturday     | Aug             | August         | PM    | 2025-08    | 2025-08-30 | Business Hours |
| Hernandez, Townsend and Montgomery | 2025-06-05 20:05:39 | 2025 | 6     | 5   | 20   | 5      | 39     | 2       | 23   | 4          | Thu           | Thursday     | Jun             | June           | PM    | 2025-06    | 2025-06-05 | After Hours    |
| Arroyo and Sons                    | 2025-05-29 15:22:54 | 2025 | 5     | 29  | 15   | 22     | 54     | 2       | 22   | 4          | Thu           | Thursday     | May             | May            | PM    | 2025-05    | 2025-05-29 | Business Hours |
| Munoz Group                        | 2025-07-16 04:56:20 | 2025 | 7     | 16  | 4    | 56     | 20     | 3       | 29   | 3          | Wed           | Wednesday    | Jul             | July           | AM    | 2025-07    | 2025-07-16 | After Hours    |
| Martin Inc                         | 2025-01-17 10:25:24 | 2025 | 1     | 17  | 10   | 25     | 24     | 1       | 3    | 5          | Fri           | Friday       | Jan             | January        | AM    | 2025-01    | 2025-01-17 | Business Hours |

Practical usage examples

Analyze sales by weekday

SELECT
    weekday_full,
    COUNT(*) AS transaction_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM v_customer), 2) AS percentage
FROM v_customer
GROUP BY weekday_full, isoweekday
ORDER BY isoweekday;

| weekday_full | transaction_count | percentage |
|--------------|------------------:|-----------:|
| Monday       | 75                | 15.0       |
| Tuesday      | 66                | 13.2       |
| Wednesday    | 70                | 14.0       |
| Thursday     | 93                | 18.6       |
| Friday       | 72                | 14.4       |
| Saturday     | 60                | 12.0       |
| Sunday       | 64                | 12.8       |

Compare morning vs. afternoon sales

SELECT
    am_pm,
    COUNT(*) AS transaction_count,
    AVG(hour) AS avg_hour
FROM v_customer
GROUP BY am_pm
ORDER BY transaction_count DESC;

| am_pm | transaction_count |      avg_hour      |
|-------|------------------:|-------------------:|
| PM    | 254               | 17.775590551181104 |
| AM    | 246               | 5.109756097560975  |

Analyze weekend sales by month

SELECT
    monthname_full,
    COUNT(*) AS weekend_sales,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM v_customer
WHERE weekday_short IN ('Sat', 'Sun')
GROUP BY month, monthname_full
ORDER BY weekend_sales DESC;

| monthname_full | weekend_sales | percentage |
|----------------|--------------:|-----------:|
| April          | 23            | 18.55      |
| February       | 16            | 12.9       |
| September      | 15            | 12.1       |
| May            | 15            | 12.1       |
| August         | 14            | 11.29      |
| January        | 13            | 10.48      |
| March          | 11            | 8.87       |
| June           | 10            | 8.06       |
| July           | 7             | 5.65       |

Business hours analysis

SELECT
    time_category,
    COUNT(*) AS transactions,
    weekday_full,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY time_category), 2) AS category_percentage
FROM v_customer
GROUP BY time_category, weekday_full
ORDER BY time_category, transactions DESC;

| time_category  | transactions | weekday_full | category_percentage |
|----------------|-------------:|--------------|--------------------:|
| After Hours    | 65           | Thursday     | 19.88               |
| After Hours    | 51           | Monday       | 15.6                |
| After Hours    | 50           | Tuesday      | 15.29               |
| After Hours    | 44           | Friday       | 13.46               |
| After Hours    | 43           | Wednesday    | 13.15               |
| After Hours    | 38           | Saturday     | 11.62               |
| After Hours    | 36           | Sunday       | 11.01               |
| Business Hours | 28           | Thursday     | 16.18               |
| Business Hours | 28           | Sunday       | 16.18               |
| Business Hours | 28           | Friday       | 16.18               |
| Business Hours | 27           | Wednesday    | 15.61               |
| Business Hours | 24           | Monday       | 13.87               |
| Business Hours | 22           | Saturday     | 12.72               |
| Business Hours | 16           | Tuesday      | 9.25                |

Adding seasonal analysis

You can extend the view to include seasonal patterns.

-- Season of the year
CASE 
    WHEN MONTH(dtm) IN (12, 1, 2) THEN 'Winter'
    WHEN MONTH(dtm) IN (3, 4, 5) THEN 'Spring'
    WHEN MONTH(dtm) IN (6, 7, 8) THEN 'Summer'
    WHEN MONTH(dtm) IN (9, 10, 11) THEN 'Autumn'
END AS "season",

-- Time of day classification
CASE
    WHEN HOUR(dtm) BETWEEN 5 AND 11 THEN 'Morning'
    WHEN HOUR(dtm) BETWEEN 12 AND 16 THEN 'Afternoon'
    WHEN HOUR(dtm) BETWEEN 17 AND 21 THEN 'Evening'
    ELSE 'Night'
END AS "day_period"

Conclusion

1. Centralize datetime logic: Instead of scattering datetime functions throughout your queries, create a single source of truth in a view.

2. Improve readability: Queries become much simpler and more intention-revealing when you use descriptive column names like weekday_full instead of complex function calls.

3. Ensure consistency: When multiple analysts work with the same data, a timestamp view ensures everyone uses the same definitions and calculations.

4. Accelerate development: With pre-calculated date components, you can quickly build reports and dashboards without repeatedly looking up function syntax.

5. Working with timestamps without all the hassle is definitely possible. 🙂