{"id":1027,"date":"2014-11-12T17:28:04","date_gmt":"2014-11-12T22:28:04","guid":{"rendered":"http:\/\/xlinesoft.com\/blog\/?p=1027"},"modified":"2014-11-18T17:13:17","modified_gmt":"2014-11-18T22:13:17","slug":"datetime-handling-in-web-applications","status":"publish","type":"post","link":"https:\/\/xlinesoft.com\/blog\/2014\/11\/12\/datetime-handling-in-web-applications\/","title":{"rendered":"Date\/time handling in web applications"},"content":{"rendered":"

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. <\/p>\n

When copying and pasting code from this article to your application replace table name (mytable) and field names (posted, dob). <\/p>\n

MySQL<\/h2>\n

Calculating age<\/p>\n

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - \r\n(DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) \r\nfrom mytable AS age<\/pre>\n
30 days before date<\/p>\n
select posted, DATE_SUB(posted, INTERVAL 30 DAY) from mytable<\/pre>\n
Date plus 30 days<\/p>\n
select posted, DATE_ADD(posted, INTERVAL 30 DAY) from mytable<\/pre>\n
Difference between two dates in days<\/p>\n
select posted, DATEDIFF(now(), posted) from mytable<\/pre>\n

\nDifference between two dates in hours:mins:seconds format<\/p>\n
select time_format(timediff(posted,CURDATE()),'%H:%m:%s') from mytable<\/pre>\n
Difference between two dates in hours<\/p>\n
select time_format(timediff(posted,CURDATE()),'%H') from mytable<\/pre>\n
Difference between two dates in months<\/p>\n
select posted, TIMESTAMPDIFF(MONTH, now(), posted) from mytable<\/pre>\n
Display data from last 30 days<\/p>\n
select * from mytable \r\nWHERE posted BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()<\/pre>\n
Display data from last 7 days<\/p>\n
select * from mytable \r\nWHERE posted BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()<\/pre>\n
Display month to date data<\/p>\n
select * from mytable \r\nWHERE YEAR(posted) = YEAR(CURDATE()) AND MONTH(posted) = MONTH(CURDATE())<\/pre>\n
Display year to date data<\/p>\n
select * from mytable \r\nWHERE YEAR(posted) = YEAR(CURDATE())<\/pre>\n
Display today's data<\/p>\n
SELECT * FROM mytable WHERE DATE(posted) = CURDATE()<\/pre>\n
Display data from yesterday<\/p>\n
SELECT * FROM mytable WHERE DATE(posted) = CURDATE()-1<\/pre>\n
SQL Server<\/h2>\n
Calculating age<\/p>\n
SELECT DATEDIFF(hour,dob,GETDATE())\/8766 AS age\r\nfrom mytable<\/pre>\n
30 days before date<\/p>\n
SELECT posted, dateadd(day, -30, posted)\r\nfrom mytable<\/pre>\n
Date plus 30 days<\/p>\n
SELECT posted, dateadd(day, 30, posted)\r\nfrom mytable<\/pre>\n
Date difference in days<\/p>\n
SELECT posted,  DATEDIFF(day,posted,getdate())\r\nfrom mytable<\/pre>\n
Time difference formatted hours:mins:seconds<\/p>\n
SELECT  convert(varchar(5),DateDiff(s, posted, getdate())\/3600)+\r\n':'+convert(varchar(5),DateDiff(s, posted, getdate())%3600\/60)+\r\n':'+convert(varchar(5),(DateDiff(s, posted, getdate())%60))\r\nfrom mytable<\/pre>\n
Time difference in hours<\/p>\n
SELECT posted,  DATEDIFF(hour,posted,getdate())\r\nfrom mytable<\/pre>\n
Time difference in months<\/p>\n
SELECT posted,  DATEDIFF(month,posted,getdate())<\/pre>\n
from mytable<\/p>\n
Last 30 days data<\/p>\n
select * from mytable \r\nWHERE posted BETWEEN getdate() - 30 AND getdate()<\/pre>\n
Last 7 days data<\/p>\n
select * from mytable \r\nWHERE posted BETWEEN getdate() - 7 AND getdate()<\/pre>\n
Month to date<\/p>\n
SELECT * FROM mytable\r\nWHERE YEAR(posted) = YEAR(getdate())\r\nAND MONTH(posted) = MONTH(getdate())\r\nAND posted < getdate()<\/pre>\n
Year to date<\/p>\n
SELECT * FROM mytable\r\nWHERE YEAR(posted) = YEAR(getdate())\r\nAND posted < getdate()<\/pre>\n
Display today's data<\/p>\n
select * from mytable\r\nwhere CONVERT (DATE, posted) = CONVERT (DATE, GETDATE()) <\/pre>\n
Display data from yesterday<\/p>\n
select * from mytable\r\nWHERE posted >= dateadd(day,datediff(day,1,GETDATE()),0)\r\nAND posted < dateadd(day,datediff(day,0,GETDATE()),0)<\/pre>\n
Microsoft Access<\/h2>\n
Calculating age<\/p>\n
SELECT DateDiff (\"yyyy\",dob, Date())+(Date() < DateSerial(Year( Date()), Month(dob), Day(dob))) AS Age\r\nFROM mytable<\/pre>\n
30 days before date<\/p>\n
SELECT posted, DateAdd('d',-30,posted)\r\nFROM mytable<\/pre>\n
Date plus 30 days<\/p>\n
SELECT posted, DateAdd('d',30,posted)\r\nFROM mytable<\/pre>\n
Date difference in days<\/p>\n
SELECT posted, DateDiff('d',posted, Now())\r\nFROM mytable<\/pre>\n
Time diffeence in hours<\/p>\n
SELECT posted, DateDiff('h',posted, Now())\r\nFROM mytable<\/pre>\n
Time diffeence in months<\/p>\n
SELECT posted, DateDiff('m',posted, Now())\r\nFROM mytable<\/pre>\n
Last 30 days data<\/p>\n
SELECT *  \r\nFROM mytable \r\nwhere DateDiff(\"d\",posted,Now())<=30 and DateDiff(\"d\",posted,Now())>=0<\/pre>\n
Last 7 days data<\/p>\n
SELECT *  FROM mytable \r\nwhere DateDiff(\"d\",posted,Now())<=7 and DateDiff(\"d\",posted,Now())>=0<\/pre>\n
Month to date<\/p>\n
SELECT * FROM mytable \r\nwhere year(posted)=year(Now())\r\nand MONTH(posted) = MONTH(Now())\r\nAND posted < Now()<\/pre>\n
Year to date<\/p>\n
SELECT * FROM mytable \r\nwhere year(posted)=year(Now())\r\nAND posted < Now()<\/pre>\n
Display today's data<\/p>\n
select * from mytable\r\nwhere posted=Date()<\/pre>\n
Display data from yesterday<\/p>\n
SELECT * FROM mytable \r\nwhere posted=Date()-1<\/pre>\n
PHP<\/h2>\n
Calculating age<\/p>\n
$time1 = new DateTime('1971-03-10');\r\n$time2 = new DateTime('today');\r\necho $time2->diff($time1)->y;<\/pre>\n
30 days before date<\/p>\n
echo date(\"Y-m-d\", strtotime('-30 days'));<\/pre>\n
Date plus 30 days<\/p>\n
echo date(\"Y-m-d\", strtotime('+30 days'));<\/pre>\n
Date difference in days<\/p>\n
$time1 = new DateTime('2014-06-10');\r\n$time2 = new DateTime('today');\r\necho $time2->diff($time1)->days;<\/pre>\n
Time difference in hours<\/p>\n
$time1 = new DateTime('2014-06-09 18:32');\r\n$time2 = new DateTime('2014-06-10 7:15');\r\necho $time2->diff($time1)->h;<\/pre>\n
Date\/Time difference formatted <\/p>\n
$time1 = new DateTime('2014-06-10');\r\n$time2 = new DateTime('today');\r\n\/\/ %a will output the total number of days\r\necho $time2->diff($time1)->format('%a total days');\r\n\/\/ months and days\r\necho $time2->diff($time1)->format('%m months, %d days');<\/pre>\n
Date diffeence in months<\/p>\n
$time1 = new DateTime('2014-06-10');\r\n$time2 = new DateTime('today');\r\necho $time2->diff($time1)->m;<\/pre>\n
Yesterday date<\/p>\n
echo date(\"F j, Y\", time() - 60 * 60 * 24);<\/pre>\n","protected":false},"excerpt":{"rendered":"
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...<\/span>Continue Reading “Date\/time handling in web applications”<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,1,8],"tags":[],"_links":{"self":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1027"}],"collection":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/comments?post=1027"}],"version-history":[{"count":20,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"predecessor-version":[{"id":1048,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1027\/revisions\/1048"}],"wp:attachment":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}