6

Dates are stored in DB as such: "2011-05-26 11:00:00" in a datetime field. I'd like to find all rows where the date is greater than the first of this month, ie: 2011-05-01 09:00:00

The date formats in the DB can not be changed. What MySQL function can I use to convert the date in the DB to a format that can handle comparison? I'm guessing the best format for "first of the month" is a unix timestamp?

The time values in the dates are always present but only office hours, so from 09:00 to 17:00.

jpaugh
  • 6,634
  • 4
  • 38
  • 90
stef
  • 26,771
  • 31
  • 105
  • 143

7 Answers7

12

If it's stored in a DATETIME field, just query on WHERE date > '2011-05-01 09:00:00'.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • @deceze... What if the date is generating dynamically? If next time date id 2001-06-12 05:00:00? How will you calculate the first date of this date? – Awais Qarni May 24 '11 at 13:28
  • @Awais That's quite a separate question, but it's not hard to find the first of the month in PHP and format it with `date('Y-m-d H:i:s')`. See @George's answer. – deceze May 25 '11 at 06:11
  • Thanks dude I have understood from George answer. – Awais Qarni May 25 '11 at 06:46
4
$firstDayOfMonth = date( 'Y-m-d H:i:s', mktime(0,0,0,date('n'),1,date('Y'));
$query = "SELECT * FROM `table` WHERE `date` >= '$firstDayOfMonth'";
George Cummins
  • 28,485
  • 8
  • 71
  • 90
2

Something like this perhaps?

$oDateTime = new DateTime();
$sQuery = "SELECT * FROM table WHERE date >= ".$oDateTime->format("Y-m")."-01 09:00:00";
Wesley van Opdorp
  • 14,888
  • 4
  • 41
  • 59
0

Compare to the datetime directly:

WHERE date_field >= '2011-05-01 00:00:00'
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
0

You can use TIMESTAMPDIFF function in MySQL to compare time,
FROM_UNIXTIME function to format Unix timestamp as date.

More mysql data and time functions and examples are available in MySQL Reference Manual date and time function .

YeJiabin
  • 1,038
  • 1
  • 9
  • 17
0

DATETIME fields can filtered just like integer fields, example:

SELECT * FROM `table` WHERE `date` > '2011-05-01 09:00:00'

In case you really want to convert to Unix timestamps, have a look at the UNIX_TIMESTAMP function.

Kris
  • 2,108
  • 18
  • 19
0

I think that you can solve it, something like this:

firstly, create first day of month in mysql format in php

$firsDay = date('Y-m-d 00:00:00', strtotime(date('Y-m').'-01 00:00:00'));

and then, use it in the query

select * from something where date >= $firsDay
Martin Vancura
  • 1