Styjun

Is it possible to measure lightning discharges as Nikola Tesla?

When India mathematicians did know Euclid's Elements?

How can I place the product on a social media post better?

Do I have to worry about players making “bad” choices on level up?

Find the coordinate of two line segments that are perpendicular

Why is current rating for multicore cable lower than single core with the same cross section?

Are Boeing 737-800’s grounded?

Is it possible to Ready a spell to be cast just before the start of your next turn by having the trigger be an ally's attack?

Illegal assignment from SObject to Contact

gnu parallel how to use with ffmpeg

Is thermodynamics only applicable to systems in equilibrium?

Python "triplet" dictionary?

Why was Germany not as successful as other Europeans in establishing overseas colonies?

Reverse the word in a string with the same order in javascript

Does a creature that is immune to a condition still make a saving throw?

A question regarding using the definite article

You look catfish vs You look like a catfish

Transfer over $10k

Has any spacecraft ever had the ability to directly communicate with civilian air traffic control?

Why does processed meat contain preservatives, while canned fish needs not?

Is there a way to get a compiler for the original B programming language?

Can I get candy for a Pokemon I haven't caught yet?

Past Perfect Tense

How to creep the reader out with what seems like a normal person?

How to address moddatetime apparently bypassing precision of column?

How do you get a timestamp in JavaScript?How can I drop all the tables in a PostgreSQL database?How to switch databases in psql?How to drop a PostgreSQL database if there are active connections to it?How to start PostgreSQL server on Mac OS X?How to exit from PostgreSQL command line utility: psqlHow to change PostgreSQL user password?PostgreSQL how to set a Primary Key column with timestamp to automatically be filledOracle timestamp column dropping fractions of secondsHow to store timestamp value upto only 3 millisecond digit in PostgreSQL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

We have a database where every timestamp is defined as timestamp(3) with time zone. We noticed that in some cases we were getting back timestamps with 6 fractional digits, not 3. I tracked this down to only happening in out update_date columns, and only if it was updated via moddatetime.

Does anybody have experience with gracefully fixing this?

I tested manual inserts, updates, from literal, from now(), etc and none of those cause the issue.

I'm aware of being able to fix this by casting to ::timestamptz(3) wherever we use this field, but this will add a lot of mess, plus I am concerned about the performance if we need to do ... WHERE date_trunc('milliseconds', update_date) > other_tz3_value.

The best I've come up with would be changing the index we have on update_date to a functional index on date_trunc('milliseconds', update_date), but again, this seems clunky.

The tables in question have a trigger like:

CREATE TRIGGER mytrigger
 BEFORE UPDATE ON mytable
 FOR EACH ROW EXECUTE PROCEDURE moddatetime(update_date);

My expectation would be that no matter how a value is inserted into a timestamp(3) field, it always comes back with no more than 3 fractional digits.

postgresql timestamp precision

share|improve this question

asked Mar 22 at 19:20

AlexAlex

637

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So what does this moddatetime() do?
  
  – a_horse_with_no_name
  Mar 22 at 19:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  moddatetime is a function provided by the extension with the same name. From postgresql.org/docs/10/contrib-spi.html : F.37.5. moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.
  
  – Alex
  Mar 25 at 13:09
  
  

  
  
  
  

add a comment | 

0

We have a database where every timestamp is defined as timestamp(3) with time zone. We noticed that in some cases we were getting back timestamps with 6 fractional digits, not 3. I tracked this down to only happening in out update_date columns, and only if it was updated via moddatetime.

Does anybody have experience with gracefully fixing this?

I tested manual inserts, updates, from literal, from now(), etc and none of those cause the issue.

I'm aware of being able to fix this by casting to ::timestamptz(3) wherever we use this field, but this will add a lot of mess, plus I am concerned about the performance if we need to do ... WHERE date_trunc('milliseconds', update_date) > other_tz3_value.

The best I've come up with would be changing the index we have on update_date to a functional index on date_trunc('milliseconds', update_date), but again, this seems clunky.

The tables in question have a trigger like:

CREATE TRIGGER mytrigger
 BEFORE UPDATE ON mytable
 FOR EACH ROW EXECUTE PROCEDURE moddatetime(update_date);

My expectation would be that no matter how a value is inserted into a timestamp(3) field, it always comes back with no more than 3 fractional digits.

postgresql timestamp precision

share|improve this question

asked Mar 22 at 19:20

AlexAlex

637

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So what does this moddatetime() do?
  
  – a_horse_with_no_name
  Mar 22 at 19:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  moddatetime is a function provided by the extension with the same name. From postgresql.org/docs/10/contrib-spi.html : F.37.5. moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.
  
  – Alex
  Mar 25 at 13:09
  
  

  
  
  
  

add a comment | 

We have a database where every timestamp is defined as timestamp(3) with time zone. We noticed that in some cases we were getting back timestamps with 6 fractional digits, not 3. I tracked this down to only happening in out update_date columns, and only if it was updated via moddatetime.

Does anybody have experience with gracefully fixing this?

I tested manual inserts, updates, from literal, from now(), etc and none of those cause the issue.

I'm aware of being able to fix this by casting to ::timestamptz(3) wherever we use this field, but this will add a lot of mess, plus I am concerned about the performance if we need to do ... WHERE date_trunc('milliseconds', update_date) > other_tz3_value.

The best I've come up with would be changing the index we have on update_date to a functional index on date_trunc('milliseconds', update_date), but again, this seems clunky.

The tables in question have a trigger like:

CREATE TRIGGER mytrigger
 BEFORE UPDATE ON mytable
 FOR EACH ROW EXECUTE PROCEDURE moddatetime(update_date);

My expectation would be that no matter how a value is inserted into a timestamp(3) field, it always comes back with no more than 3 fractional digits.

postgresql timestamp precision

share|improve this question

asked Mar 22 at 19:20

AlexAlex

637

CREATE TRIGGER mytrigger
 BEFORE UPDATE ON mytable
 FOR EACH ROW EXECUTE PROCEDURE moddatetime(update_date);

share|improve this question

asked Mar 22 at 19:20

AlexAlex

637

share|improve this question

asked Mar 22 at 19:20

AlexAlex

637

asked Mar 22 at 19:20

AlexAlex

637

asked Mar 22 at 19:20

AlexAlex

637