Test=# explain SELECT * FROM t_time WHERE x = clock_timestamp() Index Only Scan using idx_time on t_time (cost=0.43.8.45 rows=1 width=8) Test=# explain SELECT * FROM t_time WHERE x = now() Let us check the difference between now() and clock_timestamp(): I have created a table containing 2 million entries as well as an index. Test=# CREATE INDEX idx_time ON t_time (x) Why is that important? Let us take a look: If you are using clock_timestamp() you will get the real timestamp. Inside a transaction time does not appear to move forward. now() returns the same timestamp within the same transaction. There is more: In PostgreSQL there is also a distinction between now() and clock_timestamp(). This is a small but important difference. Therefore the constant will be resolved, and the current timestamp will be added to the table definition. However, ‘NOW’::timestamptz is a constant. This means that the default value inserted will change over time as transactions are started and committed. +-+-+-+-įield | timestamp with time zone | | | ' 10:49:06.741606+02'::timestamp with time zoneįield | timestamp with time zone | | | now()Īs I said before: now() is a function and therefore PostgreSQL will use the function call as the default value for the column. The following example shows why:Ĭolumn | Type | Collation | Nullable | Default In this case it makes all the difference in the world. Test=# CREATE TABLE b (field timestamptz DEFAULT now()) Test=# CREATE TABLE a (field timestamptz DEFAULT 'NOW'::timestamptz) What if we want timestamps in our table definition? Using ‘NOW’::timestamptz in table definitions 10:56:21.310924+02 | 10:56:21.310924+02Īs expected both flavors of “now” will return transaction time which means that the time within the very same transaction will stay the same. Why is that relevant? At first glance it seems to make no difference: My description already contains the magic words “function” and “constant”. Most people are not aware of the fact that there is actually a difference between now() as a function and ‘NOW’::timestamptz as a constant. To make it easier for beginners, as well as advanced people, to understand this vital topic I have decided to compile some examples which are important for your everyday work. However, in PostgreSQL there are some subtle issues most people might not be aware of. If you use other applications to also connect to the same database, timestamptz will have different implications.Everybody who has ever written any kind of database application had to use time and date. If you’re using just Elixir and your application is the only one connecting to the database, then it doesn’t make a difference if you use timestamp or timestamptz. In your schema you would use something like timestamps(type: :utc_datetime) and in migration timestamps(type: :timestamptz). It’s easy to override this in your migrations. Personally I don’t find much use in timestamptz, but that’s up to everyone to decide for themselves. It does not even store the offset/timezone. There is the confusingly named timestamp with timezone but all it does is convert your input timestamp to UTC and convert it back to whatever your DB connection’s timezone is when reading. This is because PostgreSQL does not have a data type for storing a timestamp with timezone. Back when Ecto was created, Elixir didn’t even have datetime types and Ecto didn’t have timezone support.Īlso quoting my earlier answer in an earlier topic:īTW, if you need to store the timezone too (in PostgreSQL), you have no option but to use a naive datetime field and store the offset or timezone information separately. The reason why Ecto’s timestamps() are naive by default is backwards compatibility. Please use the search facilities of this forum, just a search for timestamptz returns several topics that have details about this.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |