PostgreSQL timestamp is disabled for a given postgres date with milliseconds

Question

I have a query that, unfortunately, should compare 2 timestamps. One timestamp is given to the database from the PHP date () function, which is stored as a timestamp without a time zone, so milliseconds are not added to this date. The other is a timestamp PG with a time zone. So both dates are already created and pasted into tables, here is an example of dates:

timestamp without time zone = 2012-09-19 18:13:26

Timestamp PG with time zone = 2012-09-19 18: 13: 26.893878-04

I can specify the date PG date: timestamp (0), which closed me, but, as expected, the date is rounded; 2012-09-19 18:13:27

So my question is, how can I get seconds for rounding?

Answer 1

Comparing timestamps for equality will rarely work well. What if two timestamps were taken at 2012-09-19 18:13:26.99999999 ? Clock jitter, scheduler jitter, run-time differences, etc. They can and will often drag one to the next second. It does not have to be so close to the edge for this to happen. You can try hacks with

Compare with a limited range; say 2 seconds:

 SET timezone = '+04:00'; SELECT (TIMESTAMP '2012-09-19 18:13:26')::timestamptz BETWEEN TIMESTAMPTZ '2012-09-19 18:13:26.893878-04' - INTERVAL '1' SECOND AND TIMESTAMPTZ '2012-09-19 18:13:26.893878-04' + INTERVAL '1' SECOND; 

I'm not sure if you can use anything coarser than this, because the accuracy of your PHP timestamp is 1 second.

If you know for sure that PHP always truncates the time stamp (rounds down) rather than rounding it even when it fixes the time stamp, you can roughly adjust it by adjusting the bracketing intervals. For example, to try the 1 second interval (the narrowest you can check for the given timestamp accuracy from PHP), try it, and if PHP always truncates the timestamp:

 SELECT (TIMESTAMP '2012-09-19 18:13:26')::timestamptz BETWEEN TIMESTAMPTZ '2012-09-19 18:13:26.893878-04' - INTERVAL '1' SECOND AND TIMESTAMPTZ '2012-09-19 18:13:26.893878-04'; 

Personally, I would add at least another 0.1 seconds to each side to make sure:

 SELECT (TIMESTAMP '2012-09-19 18:13:26')::timestamptz BETWEEN TIMESTAMPTZ '2012-09-19 18:13:26.893878-04' - INTERVAL '1.1' SECOND AND TIMESTAMPTZ '2012-09-19 18:13:26.893878-04' + INTERVAL '0.1' SECOND; 

If you really insist on checking equality, use:

 regress=# SELECT date_trunc('second', TIMESTAMPTZ '2012-09-19 18:13:26.893878-04'); date_trunc ------------------------ 2012-09-19 18:13:26-04 (1 row) 

but keep in mind that it is dangerous and incorrect to check two separate timestamps for equality.

Answer 2

To round to second use date_trunc('seconds', :timestamp)

Example:

 select date_trunc('seconds', '2012-09-19 18:13:26.893878-04'::timestamp) = '2012-09-19 18:13:26'::timestamp; 

This gives t (true)