CODEWITHSUNDEEP
ruby-on-railspostgresqldatetimetimezone
lkartono
lkartono

Reputation: 2393

Rails, Postgres and Timezone

I have table which have a datetime field named date. When doing a POST in order to insert a new row, the date sent from the client (browser) looks like 2015-11-20T14:30:00+10:00 which is actually a correct date and timezone.

However, inside Postgres this date has been inserted as 2015-11-20 04:30:00.000000, which as you can see, is not at all the same as above. I know the problem is related to the timezone. But I cannot seems to figure out a fix.

For information, I have configured my app timezone :

class Application < Rails::Application
  config.time_zone = 'Brisbane'
end

Ideas?

Upvotes: 3

Views: 4962

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241460

2015-11-20T14:30:00+10:00 means that the local time of 14:30 is 10 hours ahead of UTC. Your database field reflects the correct UTC value of 04:30. This is often the desired behavior, especially if the value represent a timestamp - the date and time something occured (past tense).

In PostgreSQL, there are two different types of timestamp fields (reference)

  • The TIMESTAMP WITH TIME ZONE field accepts an input that contains a time zone offset. It then converts the value to UTC for storage. On retrieval, it uses the session's timezone setting.

  • The TIMESTAMP, or TIMESTAMP WITHOUT TIME ZONE simply stores the date and time given, ignoring any offset, and not converting to UTC.

Most of the time, you should indeed use TIMESTAMP WITH TIME ZONE. You should only use TIMESTAMP WITHOUT TIME ZONE if you need to retain the local date and time value, such as in scheduling of future events and calculation of business hours. And for those scenarios, it often makes more sense to split date and time into separate DATE and TIME fields.

One last thing - if you can avoid it, avoid using Rails time zones and use standard tzdb zones. "Australia/Brisbane" is the full tzdb identifier equivalent to the Rails "Brisbane" time zone. Refer to the section on Rails time zones at the bottom of the timezone tag wiki.

Upvotes: 8

baron816
baron816

Reputation: 701

I found this gem to be incredibly useful and easy for correctly setting the time https://github.com/kbaum/browser-timezone-rails

Upvotes: 0

Related Questions