Getting ready for SQL interviews?
Between timestamp, date, and time knowledge varieties, SQL date/time capabilities might be fairly complicated. On this weblog, I’ll try to map out the frequent knowledge/time instructions in SQL interviews utilizing PostgreSQL syntax.
Earlier than we begin to code, let’s first pause and take into consideration the weather in date/time representations. We all know that point is linear, goes a method, and might be represented mathematically as a vector.
The 2 key parts on the time vector t are second and period. These phrases are in all probability not probably the most correct, however since key phrases like timestamp and interval are taken by SQL, let’s use totally different phrases for now to know the idea.
- Second refers back to the cut up second when an incident happens. A second is instantaneous, so it’s represented as a dot on the vector t. t1 and t2 are moments.
- Length refers back to the time span between two moments. A period is made up of numerous moments and has a starting and an finish.
Due to this fact, we actually simply want to know the 2 parts and the conversion between these two parts to grasp date/time capabilities in SQL.
Timestamp offers us probably the most data. A timestamp might be exact to milliseconds and informative to incorporate time zone, however more often than not, a second of the format YYYY-MM-DD HH:MM:SS will suffice.
Timestamp might be cut up into two elements, date and time, by casting a timestamp right into a date or time object (shorthand ::). Nevertheless, it’s not advisable to forged a date or time again to a timestamp as a result of you can be lacking half of the knowledge within the timestamp or SQL will throw an error.
Loads of data might be extracted from a timestamp, akin to 12 months, month, day, hour, minute, second, and so forth. Nevertheless, not all data might be extracted from date or time objects alone. For instance, you can not extract the 12 months from time 01:50:39.
Listed here are just a few examples in PostgreSQL code:
Creating second
As a rule, you’re given a second in a SQL interview query. For instance, it might be the login time of a person, the second a person sends a message to a different, or the second a buyer locations an order. Nevertheless, this isn’t assured. Typically, you merely have to create your second.
The current: 99% of the time, we have to use the current second for some calculation. CURRENT_TIMESTAMP and NOW() provide the very same timestamp of the current second. Extra variations are offered within the picture above. Please be aware that there isn’t any such factor as a LOCALDATE.
Personalized second: It’s uncommon to come across situations the place one must customise a selected second, however if you happen to do, MAKE_TIMESTAMP will do the trick. Extra variations are offered within the picture above.
From one second to a different
DATE_TRUNC converts one second to a different. It really works like a flooring operate that resets a timestamp to the start of 12 months/month/week/day/hour/… of the timestamp.
You may enter a date, as an alternative of a timestamp, to the operate DATE_TRUNC and it’ll output outcomes by filling within the unknown time with zeros. Nevertheless, inputting a time will throw an error.
Now, let’s speak about period. Length corresponds to interval in PostgreSQL, which is offered within the format of ‘20 years 11 months 6 days’.
We will borrow the capabilities EXTRACT and DATE_PART once more to get the particular 12 months/month/day/hour/minute/second/… of the interval. The AGE operate offers us a extra holistic view. Please be aware that if just one enter is given to the AGE operate, it’ll routinely examine the enter with the present timestamp, however the precision of the output shall be constant along with your enter.
Placing collectively moments and intervals
Realizing that timestamp2-timestamp1=interval, so long as we all know two parts of the equation, we are able to calculate the third. Due to this fact, we are able to use timestamps and intervals to acquire different timestamps.
