Jump to content

Retrieving unix date and formatting


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 post
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 post
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 post
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 post
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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...