When your schema looks like:
schema "users" do
...
timestamps()
end
How would you convert these to use :utc_timestamps?
salman
Nicd
ourway
According to THIS reference, we should almost never use timestamp (without time zone).
Don’t use timestamp (without time zone)
Don’t use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.
Why not?
timestamptz records a single moment in time. Despite what the name says it doesn’t store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it’ll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.
Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.
timestamp (also known as timestamp without time zone) doesn’t do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don’t know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.
So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.
When should you?
If you’re dealing with timestamps in an abstract way, or just saving and retrieving them from an app, where you aren’t going to be doing arithmetic with them then timestamp might be suitable.
3
ourway
Based on Postgres wiki:
Don’t use timestamp (without time zone) to store UTC times
Storing UTC values in a
timestamp without time zonecolumn is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support.Use
timestamp with time zoneinstead.Why not?
Because there is no way for the database to know that UTC is the intended timezone for the column values.
This complicates many otherwise useful time calculations. For example, “last midnight in the timezone given by u.timezone” becomes this:
date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'And “the midnight prior to
x.datecolin u.timezone” becomes this:date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'When should you?
If compatibility with non-timezone-supporting databases trumps all other considerations.
5
jung-hunsoo
When You create a new record in default Ecto timestamp, it’ll be created with UTC datetime if you set the DB time zone properly.
Here is the real data.
My local PostgreSQL DB has time zone of ‘Asia/Seoul’(+09:00).
There’s a record created at 2019-12-26 10:50:23.
And in psql, it has 2019-12-26 01:50:23 in inserted_at.
But personally, I don’t think managing/manipulating tz on DB layer is a good idea. Your app will be dependent on PostgreSQL. Being aware of tz in timestamp is nonsense and a root of horror. Timestamp is best when used as UNIX timestamp.
2