Re: BUG #4972: RFE: convert timestamps to fractional seconds

The following bug has been logged online:

Bug reference: 4972
Logged by: Richard Neill
Email address: rn214(at)cam(dot)ac(dot)uk
PostgreSQL version: 8.3.7
Operating system: Linux
Description: RFE: convert timestamps to fractional seconds
Details:

Postgresql has a huge range of functions to convert things TO timestamp
format, but no way to convert back again.

For example:

* Convert a timestamp into a number of seconds since
the epoch. This can be done in an ugly way using EXTRACT epoch FROM
timestamp, but only to integer
precision. If I want to keep the microseconds, and get a float, it's not
possible. [Also, this is such a common requirement that it should probably
have a dedicated function, such as "time()" or maybe "epoch()". In PHP, this
is done by strtotime().]

* Division of a timestamp by an interval should result in something
dimensionless.

* So, for example, to check whether two timestamps (ts1 and ts2) are less
than 2.5 seconds apart, (returning boolean), I'd like to be able to do at
least one of:

abs(time(ts1 - ts2)) < 2.5
#A "time" function converts timestamp to
#sec.us since epoch)

abs(cast (ts1 - ts2) as double) < 2.5
#cast to double, might have to implicitly divide
#by the unit of "1 second"

(ts1 - ts2) / INTERVAL '1 second' < 2.5
#Divide 2 dimensioned quantities to get
#a dimensionless one.

Currently, it's necessary to do something really really long-winded, eg:

(ts1 - ts2 >= 0 AND ts1 - ts2 < interval '2.5 seconds') OR (ts2 - ts1 >= 0
AND ts2 - ts1 < interval '2.5 seconds')

BTW,The abs() function doesn't work on an INTERVAL, though there is no
reason why it shouldn't.

Thanks - Richard

"Richard Neill" <rn214(at)cam(dot)ac(dot)uk> writes:
> * Convert a timestamp into a number of seconds since
> the epoch. This can be done in an ugly way using EXTRACT epoch FROM
> timestamp, but only to integer precision.

Uh, nonsense.

regression=# select extract(epoch from now());
date_part
------------------
1249884955.29859
(1 row)

It might've been like that back around 7.1, but not in any currently
supported version.

> * Division of a timestamp by an interval should result in something
> dimensionless.

This isn't a particularly sane thing to think about, because intervals
aren't single numbers.

regards, tom lane

On Monday 10 August 2009 03:41:06 Richard Neill wrote:
> * Division of a timestamp by an interval should result in something
> dimensionless.

What would be the semantics of this? What's today divided by 2 hours?

On Monday 10 August 2009, Richard Neill wrote:
> * So, for example, to check whether two timestamps (ts1 and ts2) are less
> than 2.5 seconds apart, (returning boolean), I'd like to be able to do at
> least one of:
>
> abs(time(ts1 - ts2)) < 2.5
> #A "time" function converts timestamp to
> #sec.us since epoch)
>
> abs(cast (ts1 - ts2) as double) < 2.5
> #cast to double, might have to implicitly divide
> #by the unit of "1 second"
>
> (ts1 - ts2) / INTERVAL '1 second' < 2.5
> #Divide 2 dimensioned quantities to get
> #a dimensionless one.

What is wrong with (ts1-ts2) between i1 and i2:

cdrs=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc
(GCC) 4.1.2 (Gentoo 4.1.2)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:42.3'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
f
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:42.7'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
t
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:47.3'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
t
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:47.7'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
f
(1 row)

> Currently, it's necessary to do something really really long-winded, eg:
> (ts1 - ts2 >= 0 AND ts1 - ts2 < interval '2.5 seconds') OR (ts2 - ts1 >= 0
> AND ts2 - ts1 < interval '2.5 seconds')

Not really, as you pointed out abs(interval) doesn't work for me, but a simple
between is easier than this, and intervals seem to support sign properly.

F.O.S.

Dear Peter and Tom,

Thanks for your help. Sorry for posting an incorrect bug report. I hope
there are still a few useful parts...

Tom Lane wrote:
> "Richard Neill" <rn214(at)cam(dot)ac(dot)uk> writes:
>> * Convert a timestamp into a number of seconds since
>> the epoch. This can be done in an ugly way using EXTRACT epoch FROM
>> timestamp, but only to integer precision.
>
> Uh, nonsense.
>
> regression=# select extract(epoch from now());
> date_part
> ------------------
> 1249884955.29859
> (1 row)
>

You're quite right - I stand corrected. I'm sorry - my experiment was
clearly faulty - and when I checked the documentation, I read:

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40-08');
Result: 982384720

and saw that the result was an integer. (which is correct, but it threw
me off the scent).

Aside: I still contend that this isn't a very obvious way to do it,
being hard to find in the documentation, and slightly inconsistent
because every other EXTRACT option pulls out some fraction of the field.
(eg Extract month gives the current month number, rather than the number
of whole months elapsed since the epoch). Also, a shorthand function
name for this would be helpful.

There are two places where I think the documentation on this page
http://www.postgresql.org/docs/8.3/static/functions-datetime.html
could be improved:

(a) Table 9-26. Date/Time Functions doesn't contain ANY summary for how
to get the seconds since the epoch. An initial look at EXTRACT
would make it appear irrelevant.

(b) Nowhere on the page is there a full example for getting
seconds+microseconds since the epoch

>
>> * Division of a timestamp by an interval should result in something
>> dimensionless.
>
> This isn't a particularly sane thing to think about, because intervals
> aren't single numbers.
>

Peter Eisentraut wrote:
> On Monday 10 August 2009 03:41:06 Richard Neill wrote:
>> * Division of a timestamp by an interval should result in something
>> dimensionless.
>
> What would be the semantics of this? What's today divided by 2 hours?
>

I see your point. But on the other hand, it's very common to talk about
"distance (in metres) = 300"
or "50 seconds / seconds = 50"

What I think I meant was dividing a differential timestamp by an
interval. In this case, both should be unambiguously expressed in
seconds, and the result will be dimensionless.

For example:
select interval '3 weeks' / interval '1 week';
will fail, yet

select extract (epoch from interval '3 weeks') / extract (epoch
from interval '1 week');
gives the correct answer of 3.

Do you agree that an explicit cast of a timestamp to a double should work?

Do you agree that abs() should be able to operate on an interval?
select abs( interval '-1 week');

Thanks for your help,

Richard

On Mon, Aug 10, 2009 at 8:52 AM, Richard Neill<rn214(at)cam(dot)ac(dot)uk> wrote:
> What I think I meant was dividing a differential timestamp by an interval.
> In this case, both should be unambiguously expressed in seconds, and the
> result will be dimensionless.

And what will you get when you divide 1 month by 1 day?

...Robert

Richard Neill <rn214(at)cam(dot)ac(dot)uk> writes:
> (b) Nowhere on the page is there a full example for getting
> seconds+microseconds since the epoch

Yeah, we could change that example to include a fractional part in the
timestamp to make this clearer.

> What I think I meant was dividing a differential timestamp by an
> interval. In this case, both should be unambiguously expressed in
> seconds, and the result will be dimensionless.

What you're missing is that intervals are not single numbers, and
are not simply numbers of seconds.

> Do you agree that an explicit cast of a timestamp to a double should work?

Doesn't seem like a particularly good idea. You're free to add such
a cast to your own DBs, of course.

> Do you agree that abs() should be able to operate on an interval?
> select abs( interval '-1 week');

It's not as easy as that, because (once again) intervals aren't single
numbers. For example, what should become of
abs(interval '-1 month +1 day')
The negative of this would be '1 month -1 day'. It's not real clear to
me whether abs() should give that or '1 month 1 day', ie, make all the
fields positive independently.

regards, tom lane