Jump to content

Reading Unix Timestamp Into A Regular Date Expression


svgmx5

Recommended Posts

I'm trying to search for some rows on one of my sql tables. I need to use the time stamp as the main search query, however the time stamp is saved as a unix time stamp. So the question is how do you do a search for results saved in October 2012 or 10-2012 with a Unix Time Stamp?

Link to post
Share on other sites

Yes the date is in an integer format, and i believe the SQL is 5

 

Well what i ended up doing was just a BETWEEN Query. I found a site where i can convert a date into a UNIX time stamp and just search in between two values

 

SELECT SUM(your row) FROM your_table WHERE your_row BETWEEN your_value AND your_value;

 

If anyone has a different idea i'm open to hearing it :)

 

Thanks

Link to post
Share on other sites

There is a mySql function (UNIX_TIMESTAMP) that will convert a (mySql formatted) date into a unix timestamp.

SELECT * FROM myTable WHERE CreateDate > UNIX_TIMESTAMP('2012-10-12')

 

PHP can convert (strtotime) most formatted dates into a unix timestamp to be used in the query.

 

$minTime = strtotime('10/12/2012');
$sql = 'SELECT * FROM myTable WHERE CreateDate > ' . $minTime;

 

I have an internal site that stores unix timestamps, and I really, really wish I had not done that. Ad-hoc queries against the database are a pain, because you can't see the dates/times, unless you add the functions to do the conversion. If I was starting that project today, I would want to use DATETIME or TIMESTAMP.

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.