Reading Unix Timestamp Into A Regular Date Expression

[svgmx5]

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?

[gizmola]

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?

[svgmx5]

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

[DavidAM]

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.