Thread: Fractions of seconds in timestamps
As PostgreSQL stores timestamps with a fractional part, does it mean that WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012'04-23 23:59:59' might miss records with values of f equal to 23:59:59.1234 or so?
On Wed, Apr 25, 2012 at 12:51 AM, rihad <rihad@mail.ru> wrote: > As PostgreSQL stores timestamps with a fractional part, does it mean that > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012'04-23 23:59:59' might miss > records with values of f equal to 23:59:59.1234 or so? I think so. I would recommend either using inclusive-exclusive ranges (eg WHERE f >= '2012-04-23' AND f < '2012-04-24'), or casting to date (eg WHERE date(f)='2012-04-23'). (Untested code, might have syntax wrong a bit) ChrisA
On 04/24/2012 07:51 PM, rihad wrote: > As PostgreSQL stores timestamps with a fractional part, does it mean that > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012'04-23 23:59:59' might > miss > records with values of f equal to 23:59:59.1234 or so? > Answering to myself: depends on how timestamp was defined at table creation time time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6 for the timestamp and interval types.. As the columns in question were explicitly created as timestamp(0) in the database, they store no fractional part, so no matter at which moment during the 59-th second the column is updated, it's always exactly 59-th.
On 2012-04-24, rihad <rihad@mail.ru> wrote: > As PostgreSQL stores timestamps with a fractional part, does it mean that > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59' might miss > records with values of f equal to 23:59:59.1234 or so? yes, it does. BETWEEN doesn't work well for timestamps. you have to do it the long way f >= '2012-04-23 00:00:00' AND f < '2012-04-24 00:00:00' -- ⚂⚃ 100% natural
What about using
WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'?On 04/25/2012 09:52 AM, Jasen Betts wrote:
On 2012-04-24, rihad <rihad@mail.ru> wrote:As PostgreSQL stores timestamps with a fractional part, does it mean that WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:59' might miss records with values of f equal to 23:59:59.1234 or so?yes, it does. BETWEEN doesn't work well for timestamps. you have to do it the long way f >= '2012-04-23 00:00:00' AND f < '2012-04-24 00:00:00'
On 2012-04-25, Valentin Militaru <valentin.militaru@telcor.ro> wrote: > This is a multi-part message in MIME format. > --------------050404030901030607030308 > Content-Type: text/plain; charset=UTF-8; format=flowed > Content-Transfer-Encoding: 7bit > > What about using > > WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'? > that could match the first microsecond of 2012-04-24 otherwise not a prolem :) another option is BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60' or even BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60.999999' these are reliant on documented behaviours, but documented inosyncratic behaviours, behaviours that could potentially be improved. such that it woulkd no longer be reliable. >> you have to do it the long way >> >> f>= '2012-04-23 00:00:00' AND f< '2012-04-24 00:00:00' >> this way is mathematically correct and relies on standard guaranteed behaviours only. -- ⚂⚃ 100% natural
2012/4/25 Jasen Betts <jasen@xnet.co.nz>: > On 2012-04-25, Valentin Militaru <valentin.militaru@telcor.ro> wrote: >> This is a multi-part message in MIME format. >> --------------050404030901030607030308 >> Content-Type: text/plain; charset=UTF-8; format=flowed >> Content-Transfer-Encoding: 7bit >> >> What about using >> >> WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'? >> > > that could match the first microsecond of 2012-04-24 > otherwise not a prolem :) > > another option is BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60' > > or even BETWEEN '2012-04-23 00:00:00' AND '2012-04-23 23:59:60.999999' > > these are reliant on documented behaviours, but documented > inosyncratic behaviours, behaviours that could potentially be improved. > such that it woulkd no longer be reliable. > >>> you have to do it the long way >>> >>> f>= '2012-04-23 00:00:00' AND f< '2012-04-24 00:00:00' >>> > > this way is mathematically correct and relies on standard guaranteed > behaviours only. > > -- > ⚂⚃ 100% natural Correct Jasen! In my opinion the use of BETWEEN (as it is currently defined) makes very little sense (if any) at least for time stamps. What I've seen so far with time periods are always defined as either "[...)" or "(..]".