Time comparison in SQL

Time on the form 2009-12-31 22:12:34 can be hard to compare exactly. Therefore it is useful to have some ways of simplifying this.

Using DATE_FORMAT is useful.
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y%m%d') = '20091231'
or
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y-%m-%d') = '2009-12-31'
will give you rows with column value of that day.

The following can also be used apparantly:
WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
WHERE OrderDate LIKE '01/01/2006%'
WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'

and

WHERE OrderDate BETWEEN '01/01/2006' AND '01/02/2006'

In addition, getting values where date is less than or larger than a given difference from now or a given time. On can use INTERVAL:
SELECT * FROM table WHERE createdate < DATE_SUB(NOW(),INTERVAL 3 DAY)
will give rows where createdate is before 3 days ago.

Solutions found at http://www.w3schools.com/SQL/func_date_format.asp and http://demiliani.com/blog/archive/2006/01/19/3384.aspx

Time functions in PHP

Getting time in the right format can be a challenge. The default format is unix timestamp (a number giving the number of seconds since January 1 1970 00:00:00 UTC). However one often want to extract the date in a certain format for display purposes or for use in databases.

There are some functions that are useful for doing this. They are date(),strtotime(), mktime().

mktime will return the unix timestamp from the arguments passed into it, e.g. one can give it minutes, hours, days,months, years:
mktime(0,0,0,3,20,2004) which is "20/03/2004 00:00:00" will be 1079737200.

date will return a string formatted according to the given format. Several arguments can be given, including "Y" for 2009 while "y" gives 09 etc. Examples:
$today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm
$today = date("m.d.y"); // 03.10.01
$today = date("j, n, Y"); // 10, 3, 2001
$today = date("Ymd"); // 20010310
Or
date("d, m, Y", mktime(0,0,0,3,20,2004)) // 20, 03, 2004
date("d, m, Y", strtotime("now")) // 03, 11, 2009 )
Or
currentWeek = date("W", strtotime("now")) //45 for 03.11.2009

strtotime will take a string and try to convert it into a unix timestamp. An example is "now" which will give the time right now in unix timestamp. Other examples are strtotime("10 September 2000") and strtotime("+1 day").


Sources: http://php.net/manual/en/function.date.php, http://php.net/manual/en/function.strtotime.php, http://php.net/manual/en/function.mktime.php