In past tutorials, we've worked with text and numeric data, but conveniently ignored date & timestamp data. In this SQL lesson, we'll cover how to use:
In this tutorial, we'll use the dataset from our Microsoft SQL Interview Question which contains messages sent on Microsoft Teams.
Table: Sample Data
|100||2520||6987||Send this out now!||08/16/2021 00:00:00|
|922||3601||4500||Get on the call||08/10/2022 00:00:00|
|819||2310||4500||What's the status on this?||07/10/2022 00:00:00|
|743||3601||8752||Let's take this offline||06/14/2022 00:30:00|
|990||2520||8520||Maybe it was done by the automation process.||08/19/2022 00:00:00|
If you want to find out the current date and time, here's the functions that you can use:
Let's apply these functions onto the table:
Bear in mind, the dates and times you see in the results are based on when you run this query. In this case, it's on 08/27/2023. This is why the dates might not match the values from the table — they're showing the current date and time!
Here's the first 3 rows of the results:
|100||08/16/2021 00:00:00||08/27/2023 00:00:00||07:35:15.989933+00||08/27/2023 07:35:15|
|922||08/10/2022 00:00:00||08/27/2023 00:00:00||07:35:15.989933+00||08/27/2023 07:35:15|
|819||07/10/2022 00:00:00||08/27/2023 00:00:00||07:35:15.989933+00||08/27/2023 07:35:15|
This is pretty straightforward! 😉 The function extracts a specific component (i.e. year, month, day, hour, or minute) from a date or timestamp. You can also use for the same purpose.
You'll see that both functions give the same results:
With this handy tool, you'll be able to perform fun analysis like analyzing monthly sales trend, hourly website traffic, or daily customer interactions, and calculating averages, sums, or counts!
The rounds down a date or timestamp to a specified unit of time. In other words, it trims the finer details and retains the specified unit.
Here's what's happening in the results:
|901||08/03/2022 16:43:00||08/01/2022 00:00:00||08/03/2022 00:00:00||08/03/2022 16:00:00|
|743||06/14/2022 14:30:00||06/01/2022 00:00:00||06/14/2022 00:00:00||06/14/2022 14:00:00|
|888||08/12/2022 08:45:00||08/01/2022 00:00:00||08/12/2022 00:00:00||08/12/2022 08:00:00|
The function is used to handle date and time gaps by adding and subtracting intervals such as "3 days", "5 hours", "45 minutes".
|901||08/03/2022 16:43:00||08/05/2022 16:43:00||07/31/2022 16:43:00||08/03/2022 18:43:00||08/03/2022 16:33:00|
|743||06/14/2022 14:30:00||06/16/2022 14:30:00||06/11/2022 14:30:00||06/14/2022 16:30:00||06/14/2022 14:20:00|
|888||08/12/2022 08:45:00||08/14/2022 08:45:00||08/09/2022 08:45:00||08/12/2022 10:45:00||08/12/2022 08:35:00|
converts a date or timestamp to a string with a specified format.
Here's some commonly-used datetime formats for analysis:
|ISO 8601 Date and Time||'2023-08-27 14:30:00'|
|Date and Time with 12-hour Format||'2023-08-27 02:30:00 PM'|
|Long Month Name, Day and Year||'August 27th, 2023'|
|Short Month Name, Day and Year||'Aug 27, 2023'|
|Day, Month, and Year||'27 August 2023'|
|Day of the Month||'August'|
|Day of the Week||'Saturday'|
|901||08/03/2022 16:43:00||2022-08-03 04:43:00||2022-08-03 04:43:00 PM||August 03rd, 2022||Aug 03, 2022||03 August 2022||August||Wednesday|
|743||06/14/2022 14:30:00||2022-06-14 02:30:00||2022-06-14 02:30:00 PM||June 14th, 2022||Jun 14, 2022||14 June 2022||June||Tuesday|
|888||08/12/2022 08:45:00||2022-08-12 08:45:00||2022-08-12 08:45:00 AM||August 12th, 2022||Aug 12, 2022||12 August 2022||August||Friday|
Remember in the SQL division tutorial we talked about the concept of casting, to turn decimals into integers and vice-versa?
You can use casting for date and timestamp data too! If you encounter date or timestamp data that's formatted as strings, here's two SQL functions that can help:
|08/03/2022 16:43:00||08/03/2022 00:00:00||08/27/2023 00:00:00||08/03/2022 16:43:00||08/27/2023 10:30:00|
|06/14/2022 14:30:00||06/14/2022 00:00:00||08/27/2023 00:00:00||06/14/2022 14:30:00||08/27/2023 10:30:00|
|08/12/2022 08:45:00||08/12/2022 00:00:00||08/27/2023 00:00:00||08/12/2022 08:45:00||08/27/2023 10:30:00|
Let's solve a real SQL Interview question asked by Meta/Facebook, where your given a a table of Facebook posts:
|151652||599415||07/10/2021 12:00:00||Need a hug|
|661093||624356||07/29/2021 13:00:00||Bed. Class 8-12. Work 12-3. Gym 3-5 or 6. Then class 6-10. Another day that's gonna fly by. I miss my girlfriend|
|004239||784254||07/04/2021 11:00:00||Happy 4th of July!|
|661093||442560||07/08/2021 14:00:00||Just going to cry myself to sleep after watching Marley and Me.|
|151652||111766||07/12/2021 19:00:00||I'm so done with covid - need travelling ASAP!|
Write a query to find the number of days between each user’s first post of the year and last post of the year in the year 2021. Output the user and number of the days between each user's first and last post.
Hint: You can use the aggregate functions MIN/MAX on dates too! For example, here's a SQL query that finds the most recent date a user posted:
Congrats, you've learned pretty much every major concept needed to build a foundation in SQL, and ace the SQL interview. Now it's time to go from good to great at SQL in the advanced SQL tutorial!
ADVANCED INTRO 🥵