[jeansymolanza]

I have a table called pm_msg with atime column and the following values:

Quote

07-02-2013 18:11:00
27-01-2013 16:02:44
28-01-2013 10:30:26
30-01-2013 13:30:06

I would like to convert them to Unix timestamp while running an PDO SQL query.

This is what I've done so far, but it seems to return an error. How should I go about doing it?

$sql = "SELECT * from pm_msg ORDER BY (strtotime(time)) ASC;";

[Martyr2]

I am not sure what database you are using, but if you are using MySQL, you might want to check out UNIX_TIMESTAMP() which can take a date in your format there and return a timestamp UTC. (You may need to run date_format on it first if it insists on having the year first)

Now keep in mind that it will interpret the date as being in the current timezone of the server. So you may have to adjust for time zones if you want to alter that behavior.

Now if you are wanting to do this in PHP you can use strtotime() to get the timestamp, just make sure NOT to include it right in the query itself. Pull the date from the database, store it into a variable, use strtotime() on it then.

This post has been edited by Martyr2: 07 February 2013 - 12:33 PM

[Dormilich]

doesn’t order a Date not the same way as a Timestamp (in SQL)? (well, unless the date is not a date, but a (var)char, but that’s a DB design problem)

This post has been edited by Dormilich: 07 February 2013 - 12:38 PM