Between timestamp, date, and time data types, SQL date/time functions can be quite confusing. In this blog, I will attempt to map out the common data/time commands in SQL interviews using Postgresql syntax.
Before SQL Code
Before we start to code, let’s first pause and think about the elements in date/time representations. We know that time is linear, goes one way, and can be represented mathematically as a vector.
The two key elements on the time vector t are moment and duration. These terms are probably not the most accurate, but since keywords like timestamp and interval are taken by Sql, let’s use different words for now to understand the concept.
- Moment refers to the split second when an incident occurs. A moment is instantaneous, so it’s represented as a dot on the vector t. t1 and t2 are moments.
- Duration refers to the time span between two moments. A duration is made up of countless moments and has a beginning and an end.
Therefore, we really just need to understand the two elements and the conversion between these two elements to master date/time functions in SQL.
Moment & Moment Family
Timestamp gives us the most information. A timestamp can be precise to milliseconds and informative to include time zone, but most of the time, a moment of the format YYYY-MM-DD HH:MM:SS will suffice.
Timestamp can be split into two parts, date and time, by casting a timestamp into a date or time object (shorthand ::). However, it is not advisable to cast a date or time back to a timestamp because you will be missing half of the information in the timestamp or SQL will throw an error.
A lot of information can be extracted from a timestamp, such as year, month, day, hour, minute, second, etc. However, not all information can be extracted from date or time objects alone. For example, you cannot extract the year from time 01:50:39.
Here are a few examples in PostgreSQL code:
Creating moment
More often than not, you are given a moment in a SQL interview question. For example, it could be the login time of a user, the moment a user sends a message to another, or the moment a customer places an order. However, this is not guaranteed. Sometimes, you simply need to create your moment.
The present: 99% of the time, we need to use the present moment for some calculation. CURRENT_TIMESTAMP and NOW() give you the exact same timestamp of the present moment. More variations are presented in the image above. Please note that there is no such thing as a LOCALDATE.
Customized moment: It’s rare to encounter scenarios where one needs to customize a specific moment, but if you do, MAKE_TIMESTAMP will do the trick. More variations are presented in the image above.
From one moment to another
DATE_TRUNC converts one moment to another. It works like a floor function that resets a timestamp to the beginning of year/month/week/day/hour/… of the timestamp.
You can input a date, instead of a timestamp, to the function DATE_TRUNC and it will output results by filling in the unknown time with zeros. However, inputting a time will throw an error.
Duration
Now, let’s talk about duration. Duration corresponds to interval in PostgreSQL, which is presented in the format of ‘20 years 11 months 6 days’.
We can borrow the functions EXTRACT and DATE_PART again to get the specific year/month/day/hour/minute/second/… of the interval. The AGE function gives us a more holistic view. Please note that if only one input is given to the AGE function, it will automatically compare the input with the current timestamp, but the precision of the output will be consistent with your input.
Putting together moments and intervals
Knowing that timestamp2-timestamp1=interval, as long as we know two elements of the equation, we can calculate the third. Therefore, we can use timestamps and intervals to obtain other timestamps.
Thank you for reading! I hope this blog has been helpful to you.
