Rails: Return from Query returns two different timestamp formats

Question

Model

scope :completed_at, select("(
      SELECT goals.created_at
      FROM goals
      WHERE goals.user_id = users.id
      ORDER BY goals.created_at
      LIMIT 1
    ) as completed_at
  ")

Controller

@users = User.select("email, first, last, created_at").completed_at.order("created_at ASC").limit(1).all

Resulting SQL Query

SELECT email, first, last, created_at, (
 SELECT goals.created_at
 FROM goals
 WHERE goals.user_id = users.id
 ORDER BY goals.created_at
 LIMIT 1
 ) as completed_at
 FROM "users" ORDER BY created_at ASC LIMIT 5

Sample JSON Output

"user": {
        "completed_at": "2011-06-07 15:04:56",
        "created_at": "2010-01-01T06:00:00Z",
        "email": "[email protected]",
        "first": "Test",
        "last": "User"
    }

Note how created_at is coming out as UTC, but "completed_at" is coming out local time? Any idea why or how to get them to be the same format?

Is your problem just the different formats or is it a timezone issue? If it's just the format you can use DATE_FORMAT — τεκ
– τεκ, Commented Jul 29, 2011 at 15:34
I supposed I could do that. I guess it just seems weird to me to have to. Wondering why Rails is returning different formats... DATE_FORMAT is also MySQL-specific, right? — jmccartie
– jmccartie, Commented Jul 29, 2011 at 15:42

Tim Morgan · Accepted Answer · 2011-07-29 19:19:25Z

1

Jon, I'm not sure exactly how you're generating the json, but I'd fire up the console and see if I could figure out what type of datetime object you're getting for each column.

Also, are you setting Time.zone?

rails c
> Time.zone = 'Central Time (US & Canada)'
> u = User.select("email, first, last, created_at").completed_at.first
> u.created_at.class
=> ActiveSupport::TimeWithZone
> u.completed_at.class
=> Time # (just a guess)

The module in AR that handles time zone conversion is here.

One thing to note is that AR will not TZ convert any columns not in the columns_hash, i.e. your 'completed_at' column will NOT get TZ converted. I know, this seems backwards from what you're experiencing, but it may give you a clue.

answered Jul 29, 2011 at 19:19

Tim Morgan

1,18410 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jmccartie Over a year ago

> u.completed_at.class => String # sadTrombone I guess I'll just run in_time_zone('UTC') to convert to TimeWithZone

Tim Morgan Over a year ago

Jon, I think this SO question is the same issue -- looks like AR won't convert the the timestamp out of postgres because it doesn't know it's a timestamp. stackoverflow.com/questions/5737504/…

Collectives™ on Stack Overflow

Rails: Return from Query returns two different timestamp formats

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related