Jump to content

Archived

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

svgmx5

Reading Unix Timestamp Into A Regular Date Expression

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?

Share this post


Link to post
Share on other sites

When you state that it is saved as a unix timestamp, does that mean that it is an integer value? Also, what SQL database are you using?

Share this post


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

Share this post


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.

Share this post


Link to post
Share on other sites

×
×
  • 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.