honkmaster Posted May 18, 2012 Share Posted May 18, 2012 Hi I have a column in my database which is called "lastupdated" it stores a unix date and time stamp '1331299541' Fri, 09 Mar 2012 13:25:41 GMT. I want to run a query to retrieve any activity that happens that day. $query = "SELECT * FROM contacts WHERE lastupdated = '1331299541'"; As you can see i'm quite new to this so looking to be pointed in the right direction. I know its to do with formatting the date stamp? Cheers Chris Link to comment Share on other sites More sharing options...
birbal Posted May 18, 2012 Share Posted May 18, 2012 use mktime() http://php.net/mktime to prepare the time stamp from date and time. if you wan to get every data of 9th march you have to first make the time stamp 9th march 2010 0:0:00. one day has 86400 seconds . so you when you query it you have to check which data is fall beetween that two time stamp. Link to comment Share on other sites More sharing options...
dsonesuk Posted May 18, 2012 Share Posted May 18, 2012 (edited) IF you have column 'lastupdated' with unix timestamp '1331299541', as by your state IT contains both??? you colud use "SELECT * FROM contacts WHERE FROM_UNIXTIME(lastupdated, '%Y%m%d') = FROM_UNIXTIME('1331299541', '%Y%m%d')"; this will compare year month and day, of field value and where value. you can also pre-format lastupdate as a alias, and use that for displaying "SELECT *, FROM_UNIXTIME(lastupdated, '%D-%M-%Y') as datetest FROM contacts WHERE FROM_UNIXTIME(lastupdated, '%Y%m%d') = FROM_UNIXTIME('1331299541', '%Y%m%d')"; echo $row['datetest'].'<br />';echo $row['lastupdated'].'<br />'; Edited May 18, 2012 by dsonesuk Link to comment Share on other sites More sharing options...
Guest So Called Posted May 18, 2012 Share Posted May 18, 2012 (edited) Hi I have a column in my database which is called "lastupdated" it stores a unix date and time stamp '1331299541' Fri, 09 Mar 2012 13:25:41 GMT. I want to run a query to retrieve any activity that happens that day. $query = "SELECT * FROM contacts WHERE lastupdated = '1331299541'"; Here is an elaboration on Birbal's method. Your calculation goes like this: 1. 1331299541 = Fri, 09 Mar 2012 13:25:41 GMT 2. Fri, 09 Mar 2012 13:25:41 GMT at beginning of day is Fri, 09 Mar 2012 00:00:00 GMT 3. Fri, 09 Mar 2012 00:00:00 GMT = 1331251200 4. 1331251200 + 24*60*60 = 1331337600 5. $query = "SELECT * FROM contacts WHERE lastupdated >= '1331251200' AND lastupdated < '1331337600'"; Use the various PHP time functions to convert back and forth between UNIX time and human readable dates. Edited May 18, 2012 by So Called Link to comment Share on other sites More sharing options...
Guest So Called Posted May 18, 2012 Share Posted May 18, 2012 (edited) Here is a working code example of the date calculations necessary to populate your MySQL query string: <?php date_default_timezone_set('GMT'); $unix_time = 1331299541; $x = date('n j Y', $unix_time);list($month, $day, $year) = explode(' ', $x); echo "$month $day $year"; // 3 9 2012 $start_of_day = mktime(0, 0, 0, $month, $day, $year); echo " $start_of_day"; // 1331251200 $start_of_next_day = $start_of_day + 24*60*60; echo " $start_of_next_day"; // 1331337600 ?> It would look more like this in real life: <?php function start_of_day ($unix_time) { date_default_timezone_set('GMT'); list($month, $day, $year) = explode(' ', date('n j Y', $unix_time)); return mktime(0, 0, 0, $month, $day, $year);} ?> Edited May 18, 2012 by So Called Link to comment Share on other sites More sharing options...
dsonesuk Posted May 18, 2012 Share Posted May 18, 2012 MY apparently wrong code does all that in the sql query, alone!, breaks it down field value, and value to check against to day month year so you can compare and match, you can also format the alias value at same time to produce the format you require. Link to comment Share on other sites More sharing options...
Guest So Called Posted May 18, 2012 Share Posted May 18, 2012 (edited) So I solve my problems with PHP code and you solve your problems with MySQL code. Edited May 18, 2012 by So Called Link to comment Share on other sites More sharing options...
dsonesuk Posted May 18, 2012 Share Posted May 18, 2012 But to imply Birbal has the correct answer.means that mine was wrong, compared toTo Birbal answer I would just like to elaborate. Your calculation goes like this: Link to comment Share on other sites More sharing options...
Guest So Called Posted May 18, 2012 Share Posted May 18, 2012 (edited) Okay, I've edited my post. Is there anything else I can do to make you happy today? Edited May 18, 2012 by So Called Link to comment Share on other sites More sharing options...
dsonesuk Posted May 18, 2012 Share Posted May 18, 2012 I don't know? have you got a red hot poker handy, you might be happy at the end, but I sure will be Link to comment Share on other sites More sharing options...
Guest So Called Posted May 18, 2012 Share Posted May 18, 2012 How about telling me why the forum post editor sometimes turns <?php into character entities. (I just went back and fixed it.) I don't have any pokers. Would an ice pick help? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now