3

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?

lkartono
  • 2,323
  • 4
  • 29
  • 47

2 Answers2

8

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.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Ok thanks for those clarification. I have checked to display the date (`04:30` one) from Rails and you were right, it does match my timezone. However, there's something I do not understand. Why Rails timestamp are actually saved correctly and not this one? The system I'm building is a schedule app. For maintenance, we often check inside raw database queries for statistics purpose. Seeing `04:30` instead of `14:30` is actually really hard for maintenance. – lkartono Nov 05 '15 at 05:32
  • It's hard to answer without knowing more about your specific scenios and code, but take a look through http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – Matt Johnson-Pint Nov 05 '15 at 06:16
  • Thanks for the link. Really really useful :) – lkartono Nov 05 '15 at 22:34
  • Can you explain the reasons for why to avoid Rails time zones? What is the practical difference between choosing one versus the other? – Kelsey Hannan Oct 03 '19 at 20:41
  • 1
    @KelseyHannan - see the bottom of the [timezone tag wiki](https://stackoverflow.com/tags/timezone/info). Mostly, consider that they do not include the whole world. Many time zones are missing, having no entry in the Rails mapping. They are poorly maintained, and highly opinionated. They also have no meaning outside of Rails, where IANA TZDB identifiers are interoperable across pretty much all systems. – Matt Johnson-Pint Oct 04 '19 at 18:44
0

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

baron816
  • 701
  • 4
  • 13
  • Thanks for your answer. In my case unfortunately, the server's timezone cannot depend on browser. The requirement are ALWAYS related to the server timezone, no matter where a user is located. Anyway, this gem could be useful one day or another, so thanks for sharing :) – lkartono Nov 05 '15 at 03:39