The following bug has been logged online:
Bug reference: 4972
Logged by: Richard Neill
Email address: rn214@cam.ac.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
