Did you ever have to check what the status of an indicator was exactly:
- 2 years ago
- 6 months ago
- 3 weeks ago
What is crucial in each one of those instances is the ability to calculate what the exact date at the time was, so that you’re retrieving data for corresponding periods.
It can be a bit confusing to arrive at these calculations as the different databases could have different function names, different function syntax or even no functions at all.
Let’s go through some of the most popular databases out there:
|1. SQL Server:||dateadd (period, value, timestamp)|
|2. MySQL:||date_add (timestamp, ‘interval’ value, period)*|
|3. Vertica:||timestampadd (period, value, timestamp)|
|4. PostgreSQL:||timestamp – INTERVAL ‘period’|
I know… These probably don’t make much sense – at least they looked terribly confusing when I saw them in the databases official documentation.
So let’s check out the actual SQL:
Good news is that, as you’ll have seen above, there isn’t much difference if you’d like to move backwards or forwards.
All you need to do is just change the value sign, which is great because those functions can then be used if you’d like to make some future extrapolations as well.