Обсуждение: 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

WHERE f BETWEEN '2012-04-23 00:00:00' AND '2012-04-24 00:00:00'?

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 "(..]".