Unlock the power of date and time functions in SQL to precisely handle and analyze time-stamped data. Learn to extract and manipulate specific date and time components directly from your database.
Key Insights
- Understand how date and time data are stored in databases, typically formatted from largest to smallest units (year, month, day, hour, minute, second, milliseconds), and recognize the difference between date, time, and timestamp (datetime).
- Explore common PostgreSQL functions like
now(),current_date, andcurrent_timeto extract specific date/time information, and utilize thedate_part()function to retrieve targeted components (e.g., hour, year, day of the week) from stored timestamps. - Recognize differences in date function implementations among database systems; for example, PostgreSQL’s day-of-week numbering begins at zero (Sunday), whereas SQL Server starts counting from one.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
This is a lesson preview only. For the full lesson, purchase the course here.
All right, so we're gonna switch gears a little bit. We haven't worked with dates yet. We saw functions earlier, functions like rounding functions, aggregate functions, functions that perform specific tasks.
And there's many ways that we can work with dates. And when we work with dates, you'll see this is a new type of data. And we haven't really addressed it yet, but over here, for example, there's a little clock icon indicating that we have date information.
Notice how it displays as timestamp. We can sometimes see date stamp or timestamp. We might encounter either date or time.
So date would be like today's date is January 2nd. The time is 3.08 PM. If you say date stamp or date time, notice it says date time or date time.
Datetime or timestamp includes both. You have the date and the time, so you have both. These are different types of data.
Consider casting. If you have one and you want the other, you might cast or convert from one data type to another. When we originally create these records in the database, whoever inserts those records can choose to add a timestamp as a column.
It’s not metadata, though. It's not stuff that's always there. It has to be a column inserted by the person creating the database.
So don't just assume we always have timestamps on everything. It's only there if the person creating the database gave us that information. But let’s assume they did.
When stored, the information will be displayed from large to small, starting with the year and ending with milliseconds. We’ll see a four-digit year, then a month, then a day, followed by hours, minutes, and seconds. And seconds are broken down into milliseconds. So very minute, very small fractions of a second. But it goes from big all the way down to the smallest portion of time.
There are some common date functions, and this is where things vary between PostgreSQL and SQL Server. So almost all of these things we're gonna see, just ignore the SQL Server thing because they really choose to do things differently. But in PostgreSQL, we might wanna say, what's the date and time right now? If we said now, that will go and give us the current date and time.
If we only want the date, we can use `current_date`. That just gives us the date, not date and time. If we just want the time, we can say current time to just get the time.
Do you want the date, the time, or both the date and time? So that'll give you all of that information or whatever part you want. But also, let’s say you have something stored in your database, and you wanna compare the current date or time to that. You might want to compare the data stored in your database, which could include the year, month, and day.
They may include the year, month, and day, but you might only want to compare the year. So out of that whole date, time chunk of information, you wanna break it down into just the part. They have a date part function, where we can say, let’s just get the year from some place in our database, and you can specify the column from your database.
So for example, and we’ll just ignore the SQL Server, we can say, let’s get the part of createdAt, we just want the hour. So just get the hour part of that. And so that’ll tell us what hour each product was created.
We could also get the day of the week. So D-O-W stands for day of the week. This will give us Sunday through Saturday, but not in a name, that would be nice, right? No, it gives you a number.
And they start counting at zero. As I like to say, true geeks start at zero. I'm a geek, but I don't like starting at zero.
I'm not a big fan of starting at zero. I'd like to start at one, and that's actually how SQL Server works, but for some reason, PostgreSQL starts at zero. So, zero represents Sunday, one represents Monday, and Saturday is represented by six.
So, you’ll get a number between zero and six. Okay, so that'll give you the day of the week. So it knows on the calendar in this year, on this month, this day was a Sunday or a Tuesday or whatever it is.
