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?

Improve this question

2 Answers 2

Reset to default
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.

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

5 Comments

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.
It's hard to answer without knowing more about your specific scenios and code, but take a look through stackoverflow.com/questions/2532729/…
Thanks for the link. Really really useful :)
Can you explain the reasons for why to avoid Rails time zones? What is the practical difference between choosing one versus the other?
@KelseyHannan - see the bottom of the timezone tag wiki. 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.
0

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

Improve this answer

1 Comment

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.