Re: BUG #4972: RFE: convert timestamps to fractional seconds - Mailing list pgsql-bugs

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@cam.ac.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