Snowflake
Round Down Date/Time

Snowflake DATE_TRUNC: Round Down a Date or Time

What it does

DATE_TRUNC rounds down a date, time, or timestamp to the component you specify. For example, it can truncate a specific date to the start of the year, month, or day it falls in, effectively setting smaller components (like hour, minute, etc.) to zero.

Syntax


_10
DATE_TRUNC('<date_or_time_part>', '<date_or_time_expr>')

where:

  • <date_or_time_part>: This parameter determines the precision level for truncating your date or time value. You can choose units like year, quarter, month, week, day, hour, minute, second, and even down to millisecond, microsecond, or nanosecond.
  • <date_or_time_expr>: This is the date, time, or timestamp value you want to truncate. It can be a column in a database, a timestamp, or any expression that evaluates to a date or time.

Common use cases

  • Aggregating sales data by month: If you're analyzing sales data and want to see trends month by month, you can use DATE_TRUNC to simplify your date data to the first of each month. This makes it easy to group and sum sales figures by month.

_10
SELECT
_10
DATE_TRUNC ('month', sales_date) AS month,
_10
SUM(sales_amount) AS total_sales
_10
FROM
_10
sales
_10
GROUP BY
_10
month;

  • Daily log data reports: For IT operations, simplifying timestamp data to daily granularity can help in generating daily activity or error reports.

_10
SELECT
_10
DATE_TRUNC ('day', log_timestamp) AS day,
_10
COUNT(*) AS number_of_logs
_10
FROM
_10
server_logs
_10
GROUP BY
_10
day;

  • Analyzing user activity by the hour: If you're interested in understanding user behavior on your platform throughout the day, you might want to truncate timestamps to the nearest hour.

_10
SELECT
_10
DATE_TRUNC ('hour', activity_timestamp) AS hour,
_10
COUNT(*) AS activity_count
_10
FROM
_10
user_activity
_10
GROUP BY
_10
hour;

  • Preparing data for financial quarters: When preparing financial reports, you might need to analyze revenues by quarters.

_10
SELECT
_10
DATE_TRUNC ('quarter', transaction_date) AS quarter_start,
_10
SUM(revenue) AS total_revenue
_10
FROM
_10
transactions
_10
GROUP BY
_10
quarter_start;

Examples

Event frequency analysis by month

Let's imagine we have a database tracking galactic events from "The Hitchhiker's Guide to the Galaxy," including the exact timestamps of each event. We'll use this data to analyze when these events occurred by truncating the timestamps to the first day of each month, allowing us to easily see which months were busiest with events.

Input

Table: galactic_events

event_idcharacter_nameevent_nameevent_timestamp
1Arthur DentVogon Poetry Reading2021-03-15 09:15:00.000
2Ford PrefectBabel Fish Auction2021-03-15 14:30:00.000
3Zaphod BeeblebroxPan Galactic Gargle Blaster Contest2021-06-01 20:00:00.000
4TrillianDeep Thought Debate2021-06-01 16:45:00.000
5MarvinExistential Dilemma Solving2021-09-10 13:00:00.000

Snowflake SQL Query


_10
SELECT
_10
DATE_TRUNC ('month', event_timestamp) AS event_month,
_10
COUNT(*) AS number_of_events
_10
FROM
_10
galactic_events_dupl_2
_10
GROUP BY
_10
event_month
_10
ORDER BY
_10
event_month;

Output

event_monthnumber_of_events
2021-03-01 00:00:00.0002
2021-06-01 00:00:00.0002
2021-09-01 00:00:00.0001
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free