Jump to content

Retrieving unix date and formatting


honkmaster

Recommended Posts

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

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

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 by dsonesuk
Link to comment
Share on other sites

Guest So Called
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 by So Called
Link to comment
Share on other sites

Guest So Called

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 by So Called
Link to comment
Share on other sites

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

Guest So Called

So I solve my problems with PHP code and you solve your problems with MySQL code.

Edited by So Called
Link to comment
Share on other sites

Guest So Called

Okay, I've edited my post. Is there anything else I can do to make you happy today? :)

Edited by So Called
Link to comment
Share on other sites

Guest So Called

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...