Big SQL Energy Beginner Course: Lesson 21 Date_Trunc()

Jan 21, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 21 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about Date_Trunc() in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 21: Date_Trunc()

🎥 Video

Date_trunc() is a very powerful date function that allows us to truncate (or round down) a date to the beginning of a specified period. It rewinds the clock to the first point of that date part. It basically trims a date or datetime at a specified point, and sets the unimportant more granular parts to the first part of the chosen period.

For example, truncating the date 2023-03-18 by month would produce 2023-03-01 because we’re keeping the month value and then resetting the rest of the more granular values to the first of the period— leaving us with 01 as the day instead of 18 since 01 is the first day of the month. Truncating by month will set all the dates to the FIRST day of that month and still keep the original year value.

Similarly, if we were to truncate 2023-03-18 and 2024-07-28 by year, we’d end up with 2023-01-01 and 2024-01-01 respectively because truncating by year keeps the year and sets all the more granular values equal to the first in the period. In this case, it sets both the month and dates values to 01 since 01-01 is the first month and day of the year. Truncating by year will set all the dates to the FIRST month and day of that year and still keep the original year value.

If we have a datetime with a timestamp the same thing will happen, and the timestamp parts more granular than the truncated level will be set to 0s. So if we were to truncate 2023-03-18 12:20:19 and 2024-07-28 01:02:45 by day, it would result in 2023-03-18 00:00:00 and 2024-07-28 00:00:00. The day value would be kept, and everything more granular (aka the timestamp parts) would all be reset to the first value of the period (aka 0s). Truncating by day will set all the datetimes (if applicable/available) to the FIRST timestamp of the day and still keep the original day, month, and year values.

The concept of date_trunc() is a little confusing when you first learn it, but it can be very powerful when it comes to rounding down & standardizing your dates to aggregate. Aggregating X by date is a very common interview question, so make sure you understand it well. We’ll learn the aggregation by X part in the next lesson, but let’s make sure we know how to use date_trunc() first:

select 
    order_date,
    date_trunc('year', order_date) as order_year,
    date_trunc('month', order_date) as order_month,
    date_trunc('day', order_date) as order_day
from 
    orders
;

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!