The following bug has been logged online: Bug reference: 4972 Logged by: Richard Neill Email address: rn...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
