DATE FUNCTIONS

Understand different `DATE` manipulation functions

5 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Introduction to Dates — Dealing with dates often requires extracting specific parts like the year from a longer date.
EXTRACT Function — The EXTRACT function pulls a specified date or time part from a date, time, or timestamp.
EXTRACT Granularity — Examples show extracting hour, day, month, year, and quarter, which can then be used for grouping.
DATE_TRUNC Overview — DATE_TRUNC truncates a date or timestamp to a specified position, standardizing it backward.
DATE_TRUNC Examples — Truncating to year sets everything else to the first of that year, while truncating to day zeroes out time components.
DATE_DIFF Function — DATE_DIFF calculates the difference between two dates, times, or timestamps.
DATE_DIFF Calculation — Using DATE_DIFF with 'minute' granularity calculates the duration between start and end timestamps, returning a numeric result.
Granularity Summary — All three functions generally support granularities like year, month, day, hour, and minute, assuming the data detail exists.

PDF notes

Bottom line: You will learn three essential SQL functions (EXTRACT, DATE_TRUNC, and DATE_DIFF) to manipulate and analyze date and timestamp data. You will be able to isolate specific date parts, standardize timestamps to a desired granularity, and calculate the duration between two points in time.

This lesson on DATE FUNCTIONS is hands-on and example-driven. You will learn three essential SQL functions (EXTRACT, DATE_TRUNC, and DATE_DIFF) to manipulate and analyze date and timestamp data. You will be able to isolate specific date parts, standardize timestamps to a desired granularity, and calculate the duration between two points in time.

What You'll Be Able To Do

Isolate the year, month, or hour from a full timestamp using EXTRACT.
Standardize a timestamp to the beginning of a specified period (e.g., month or day).
Calculate the time difference between two timestamps in minutes, hours, or seconds.
Determine the appropriate granularity (year, quarter, day) for date manipulation functions.
Group analytical results based on extracted or truncated date components.

Topics Covered in DATE FUNCTIONS

Introduction to Dates (0:00 - 0:15) — Dealing with dates often requires extracting specific parts like the year from a longer date.
EXTRACT Function (0:15 - 0:50) — The EXTRACT function pulls a specified date or time part from a date, time, or timestamp.
EXTRACT Granularity (0:50 - 1:30) — Examples show extracting hour, day, month, year, and quarter, which can then be used for grouping.
DATE_TRUNC Overview (1:30 - 1:55) — DATE_TRUNC truncates a date or timestamp to a specified position, standardizing it backward.
DATE_TRUNC Examples (1:55 - 2:40) — Truncating to year sets everything else to the first of that year, while truncating to day zeroes out time components.
DATE_DIFF Function (2:40 - 3:00) — DATE_DIFF calculates the difference between two dates, times, or timestamps.
DATE_DIFF Calculation (3:00 - 3:45) — Using DATE_DIFF with 'minute' granularity calculates the duration between start and end timestamps, returning a numeric result.
Granularity Summary (3:45 - 4:00) — All three functions generally support granularities like year, month, day, hour, and minute, assuming the data detail exists.

SQL Cheat Sheet

EXTRACT(part FROM timestamp) — Pulls specified date or time part from a date or timestamp
```
SELECT EXTRACT(HOUR FROM order_timestamp) FROM orders;
```
DATE_TRUNC('part', timestamp) — Truncates a date/timestamp to the start of the specified position
```
SELECT DATE_TRUNC('year', order_timestamp) FROM orders;
```
DATE_DIFF('part', start_ts, end_ts) — Calculates the difference between two dates or timestamps
```
SELECT DATE_DIFF('minute', start_ts, end_ts);
```
YEAR — Specifies the year component for extraction or truncation
```
SELECT EXTRACT(YEAR FROM order_timestamp);
```
QUARTER — Specifies the quarter component (1-4) for extraction
```
SELECT EXTRACT(QUARTER FROM order_timestamp);
```
DAY — Specifies the day component for extraction or truncation
```
SELECT DATE_TRUNC('day', order_timestamp);
```

Comparison Table

Function	Purpose	Output Type
EXTRACT	Isolates a specific date part.	Numeric value (integer).
DATE_TRUNC	Standardizes timestamp to period start.	Timestamp/Date object.
DATE_DIFF	Measures duration between two points.	Numeric value (integer).

Common Pitfalls

Mistake: Using DATE_TRUNC when you only need the numeric value of the part. Avoid: Use EXTRACT if you only need the number (e.g., 1 for January).
Mistake: Expecting DATE_TRUNC to return just the year number. Avoid: DATE_TRUNC returns a full timestamp starting at that period's beginning.
Mistake: Confusing the order of arguments in DATE_DIFF. Avoid: Always specify granularity first, then start time, then end time.

FAQs

What is the difference between EXTRACT and DATE_TRUNC? EXTRACT returns a number representing a date part (e.g., 1 for January). DATE_TRUNC returns a full timestamp standardized to the start of that period (e.g., 2024-01-01 00:00:00).
Can I use DATE_DIFF to find the difference in days? Yes, you can specify 'day' as the granularity argument in DATE_DIFF to calculate the difference in days.
Do these functions work if my column is just a DATE, not a TIMESTAMP? Yes, but you cannot use granularities finer than 'day' (like hour or minute) if the underlying data lacks that detail.

Common pitfalls

Mistake: Using DATE_TRUNC when you only need the numeric value of the part. Avoid: Use EXTRACT if you only need the number (e.g., 1 for January).
Mistake: Expecting DATE_TRUNC to return just the year number. Avoid: DATE_TRUNC returns a full timestamp starting at that period's beginning.
Mistake: Confusing the order of arguments in DATE_DIFF. Avoid: Always specify granularity first, then start time, then end time.

So dealing with dates can sometimes be challenging using SQL. As I'll give you an example, maybe what we want to do is extract a part from a date or a timestamp. Like maybe we want the year from a longer date, like 2011 from the full date which is January 8th, 2011. Okay? So let me give you a few functions that'll help a lot as you're dealing with dates when you write your SQL statements. So the first function is extract and it's similar to date part if you've heard of that before. But like the example, this is going to extract the specified date or time from a date, time or timestamp. So let me give you an example. So let's say um we have a list of orders and the timestamp for those orders. And what we want to do is from that timestamp, extract the hour. So in this example in the results, I see my timestamp and then what the extract function is done is pulled out the hour. Maybe there's cases we want to just count the number of orders by hour, we can use this to do that. Here's another example. Maybe we want to pull out the day, so we have this timestamp, we want to pull out the day, like the 6th, the 12th, the 21st, and so on. We can also use the month, right? In this case, it's pulling out January, then March, then July. So 1, 3, 7 and so on. We can also do the year. In this case, it's all the same, but it's pulling out the year. Or we can do the quarter. So in this case, the quarter looks at the month and what um quarter that month is in. And so then once we do this, we can take the results that we've extracted and use it for grouping or or something else. The next function is date trunk, which is a little bit similar but it's going to truncate a date, time or timestamp to a specified position. So here's an example. Let's use the same scenario, we've got a list of orders with a timestamp for those orders. And what we want to do is truncate the timestamp to the year. And so what this is going to do is start with the year. So truncate it to the year and everything else after that is going to be the first uh month, day, second of that year and so on. So it's truncating it backward, it's shorting it if you will. And then if we're doing the month, it's going to truncate it to the year and the month. So it gives us month level detail, but after that, like the day, the minute, the hour, are always the first of that thing. And then we could go a little deeper here and say we want to truncate it to the day. So in this case, we're just getting the the granularity of the date, but the hours, the minutes, the seconds, it's just um kind of zeroed out there. And then the next function I wanted to talk through was date diff. Okay? So we talked about um date part or extract and date trunk. Now this is date diff. So this is going to calculate the difference between two dates, times or timestamps. So here's an example. Let's say we have two timestamps. Maybe one's like the start of something or a start of a process and the next is the end of it. So we can use select in this case date diff. And then we select the granularity how we want to measure the difference. So maybe we want the the difference in minutes between these two timestamps, and we'll just call the difference that's the result. And so what we're going to get in this case is 35 minutes. So the difference between these two timestamps, they're on the same year, month and day, but the timestamp or the time is 35 minutes in difference. And then we could also use second here and the number of seconds difference, and it would be that in this case. You could still use all of the other measurements. So basically for all of these functions, you can use year, month, day, hour, minute, assuming you have that level of detail, like a full timestamp instead of just a date. So there's a lot of similarities between these functions. But it should help us when we're trying to deal with dates as we write our SQL statements in response to business questions we're trying to answer. Thanks for watching. If you like this video, click the subscribe button. And if you're interested in the SQL cheat sheet, you can find one over at codybaldwin.com.

Frequently asked questions

What is the difference between EXTRACT and DATE_TRUNC?

EXTRACT returns a number representing a date part (e.g., 1 for January). DATE_TRUNC returns a full timestamp standardized to the start of that period (e.g., 2024-01-01 00:00:00).

Can I use DATE_DIFF to find the difference in days?

Yes, you can specify 'day' as the granularity argument in DATE_DIFF to calculate the difference in days.

Do these functions work if my column is just a DATE, not a TIMESTAMP?

Yes, but you cannot use granularities finer than 'day' (like hour or minute) if the underlying data lacks that detail.