2 Replies - 725 Views - Last Post: 07 February 2013 - 12:37 PM

#1 jeansymolanza  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 77
  • Joined: 20-February 08

Convert from "d-m-Y H:i:s" format in table to UTC

Posted 07 February 2013 - 11:33 AM

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;";



Is This A Good Question/Topic? 0
  • +

Replies To: Convert from "d-m-Y H:i:s" format in table to UTC

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4437
  • View blog
  • Posts: 12,308
  • Joined: 18-April 07

Re: Convert from "d-m-Y H:i:s" format in table to UTC

Posted 07 February 2013 - 12:31 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3577
  • View blog
  • Posts: 10,442
  • Joined: 08-June 10

Re: Convert from "d-m-Y H:i:s" format in table to UTC

Posted 07 February 2013 - 12:37 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1