convert unix time to mysql datetime

rubyonrails-talk
1

Hi,

I have a unix timestamp in an xml feed that I want to convert to a mysql datetime before I put it into my database. Any hints?

Charlie

2

Let me ask this another way. Is it possible to store it in mysql as a unix timestamp, then format it the way I want it, if I ever want to display it? When I try to put it in my mysql database as a unix timestamp, it's rejected (the column format is datetime). Charlie

3

some_time = Time.now some_time.to_s(:db)

4

One other thing XML has it's own standard for time.

./script/console

some_time = Time.now

=> Thu Jun 07 12:58:50 -0400 2007

some_time.to_s(:db)

=> "2007-06-07 12:58:50"

some_time.xmlschema

=> "2007-06-07T13:02:53-04:00"

Also take a look at Time::parse class method for converting string representations of time into ruby Time objects.

5

I can't see how to use Time::parse to turn a unix epoch timestamp (like 1181083337) into a date time format I can put into the database.

I see, though, from the Class:Time document page, that Time.at can do this. But I get an error when I try:

While I'm importing xml in my controller, and before I put the time into the database, I try to convert it from unix epoch time into a datetime with time.at, like this:

      publish_date_unix = news_publish_dates[idx]     newslistings_publish_date = Time.at(publish_date_unix)

And I get this error:

can't convert String into time

Charlie

6

One more thing -- when I say, at the console

puts Time.at(1181083337)

it works -- I get

Tue Jun 05 15:42:17 -0700 2007

Charlie

7

perhaps:

Time.at(publish_date_unix.to_i)

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

8

Got it, thank you.