Date/time handling in web applications

Date/time calculations can be perform either on database side or in your server side language (PHP, ASP, C#) or even in Javascript. We’ll try to cover all those scenarios.

When copying and pasting code from this article to your application replace table name (mytable) and field names (posted, dob).

MySQL

Calculating age

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - 
(DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) 
from mytable AS age

30 days before date

select posted, DATE_SUB(posted, INTERVAL 30 DAY) from mytable

Date plus 30 days

select posted, DATE_ADD(posted, INTERVAL 30 DAY) from mytable

Difference between two dates in days

select posted, DATEDIFF(now(), posted) from mytable

Difference between two dates in hours:mins:seconds format

select time_format(timediff(posted,CURDATE()),'%H:%m:%s') from mytable

Difference between two dates in hours

select time_format(timediff(posted,CURDATE()),'%H') from mytable

Difference between two dates in months

select posted, TIMESTAMPDIFF(MONTH, now(), posted) from mytable

Display data from last 30 days

select * from mytable 
WHERE posted BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Display data from last 7 days

select * from mytable 
WHERE posted BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()

Display month to date data

select * from mytable 
WHERE YEAR(posted) = YEAR(CURDATE()) AND MONTH(posted) = MONTH(CURDATE())

Display year to date data

select * from mytable 
WHERE YEAR(posted) = YEAR(CURDATE())

Display today's data

SELECT * FROM mytable WHERE DATE(posted) = CURDATE()

Display data from yesterday

SELECT * FROM mytable WHERE DATE(posted) = CURDATE()-1

SQL Server

Calculating age

SELECT DATEDIFF(hour,dob,GETDATE())/8766 AS age
from mytable

30 days before date

SELECT posted, dateadd(day, -30, posted)
from mytable

Date plus 30 days

SELECT posted, dateadd(day, 30, posted)
from mytable

Date difference in days

SELECT posted,  DATEDIFF(day,posted,getdate())
from mytable

Time difference formatted hours:mins:seconds

SELECT  convert(varchar(5),DateDiff(s, posted, getdate())/3600)+
':'+convert(varchar(5),DateDiff(s, posted, getdate())%3600/60)+
':'+convert(varchar(5),(DateDiff(s, posted, getdate())%60))
from mytable

Time difference in hours

SELECT posted,  DATEDIFF(hour,posted,getdate())
from mytable

Time difference in months

SELECT posted,  DATEDIFF(month,posted,getdate())

from mytable

Last 30 days data

select * from mytable 
WHERE posted BETWEEN getdate() - 30 AND getdate()

Last 7 days data

select * from mytable 
WHERE posted BETWEEN getdate() - 7 AND getdate()

Month to date

SELECT * FROM mytable
WHERE YEAR(posted) = YEAR(getdate())
AND MONTH(posted) = MONTH(getdate())
AND posted < getdate()

Year to date

SELECT * FROM mytable
WHERE YEAR(posted) = YEAR(getdate())
AND posted < getdate()

Display today's data

select * from mytable
where CONVERT (DATE, posted) = CONVERT (DATE, GETDATE()) 

Display data from yesterday

select * from mytable
WHERE posted >= dateadd(day,datediff(day,1,GETDATE()),0)
AND posted < dateadd(day,datediff(day,0,GETDATE()),0)

Microsoft Access

Calculating age

SELECT DateDiff ("yyyy",dob, Date())+(Date() < DateSerial(Year( Date()), Month(dob), Day(dob))) AS Age
FROM mytable

30 days before date

SELECT posted, DateAdd('d',-30,posted)
FROM mytable

Date plus 30 days

SELECT posted, DateAdd('d',30,posted)
FROM mytable

Date difference in days

SELECT posted, DateDiff('d',posted, Now())
FROM mytable

Time diffeence in hours

SELECT posted, DateDiff('h',posted, Now())
FROM mytable

Time diffeence in months

SELECT posted, DateDiff('m',posted, Now())
FROM mytable

Last 30 days data

SELECT *  
FROM mytable 
where DateDiff("d",posted,Now())<=30 and DateDiff("d",posted,Now())>=0

Last 7 days data

SELECT *  FROM mytable 
where DateDiff("d",posted,Now())<=7 and DateDiff("d",posted,Now())>=0

Month to date

SELECT * FROM mytable 
where year(posted)=year(Now())
and MONTH(posted) = MONTH(Now())
AND posted < Now()

Year to date

SELECT * FROM mytable 
where year(posted)=year(Now())
AND posted < Now()

Display today's data

select * from mytable
where posted=Date()

Display data from yesterday

SELECT * FROM mytable 
where posted=Date()-1

PHP

Calculating age

$time1 = new DateTime('1971-03-10');
$time2 = new DateTime('today');
echo $time2->diff($time1)->y;

30 days before date

echo date("Y-m-d", strtotime('-30 days'));

Date plus 30 days

echo date("Y-m-d", strtotime('+30 days'));

Date difference in days

$time1 = new DateTime('2014-06-10');
$time2 = new DateTime('today');
echo $time2->diff($time1)->days;

Time difference in hours

$time1 = new DateTime('2014-06-09 18:32');
$time2 = new DateTime('2014-06-10 7:15');
echo $time2->diff($time1)->h;

Date/Time difference formatted

$time1 = new DateTime('2014-06-10');
$time2 = new DateTime('today');
// %a will output the total number of days
echo $time2->diff($time1)->format('%a total days');
// months and days
echo $time2->diff($time1)->format('%m months, %d days');

Date diffeence in months

$time1 = new DateTime('2014-06-10');
$time2 = new DateTime('today');
echo $time2->diff($time1)->m;

Yesterday date

echo date("F j, Y", time() - 60 * 60 * 24);