HOWTO Add/Subtract Days or Time

Objective

Show different database techniques for determining the date in the past (or future). The following will show you HOWTO get three days ago based on CURRENT_TIMESTAMP. All will generate something like this.

CurrentTime	ThreeDaysAgo
2015–02–01 11:42:04	2015–01–29 11:42:04

Below you will find information MySQL, SQL Server, SQLITE3, and PostgreSQL.

MySQL

Using MySQL, take a timestamp DB property (like CURRENT_TIMESTAMP for “now” or a property from a table). Then add + or - followed by the keyword INTERVAL. Then a number followed by a unit like day if you (for example) want to add or subtract a certain amount of days. The fllowing code subtracts three days from now.

mysql> SELECT CURRENT_TIMESTAMP as CurrentTime,
         CURRENT_TIMESTAMP - INTERVAL 3 day as ThreeDaysAgo;

For More Information

Microsoft SQL Server

Using SQL Server, the end result is the same but they use a function to do the same thing. Using the DateAdd function, you specify a unit, quantity, and DB property. In the following example I use “now” and subtract three days from it.

SELECT CURRENT_TIMESTAMP as CurrentTime,  
    DateAdd(day,-3,CURRENT_TIMESTAMP) as ThreeDaysAgo;

SQLITE3

For SQLITE, you need two wrapper the date time property in the datetime(…) function with a paramter telling it what manipulations you would like to do. In this case, I want to do subtract three days.

sqlite> SELECT CURRENT_TIMESTAMP as CurrentTime, 
datetime(CURRENT_TIMESTAMP, '-3 days') as ThreeDaysAgo

For More Information

PostgreSQL

I have not had a chace to work this out for postgresql yet.
For Information on this point visit

Conclusions

Using the above syntax, you will be able to manipulate dates to add or subtract time as your code requires.