I have a table of raw data as
timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp value in the data set. Then I will call extract(epoch) on that interval to get its length in seconds in double precision. I have set
extra_float_digits = 1 so I have 6 decimal place precision in the seconds (so microsecond precision).
I then want to multiply the return from
extract(epoch) by 1 million to get the number of microseconds in double precision, which I want to then convert to
bigint and save as a column in the table
time_pyramid. Along with each record I want to store a 0 and a 1 with it to indicate a layer number and a count number, so
time_pyramid will have 3 columns. My query looks like this:
INSERT INTO TIME_PYRAMID SELECT 0, CAST (time_converted AS bigint) AS "time", 1 FROM ( SELECT EXTRACT(EPOCH FROM ("time" - MIN("time"))) * 1000000 AS time_converted FROM TIME_RAW GROUP BY time ) x;
However, I get all 0's for my time value, so clearly something is not working. Do I need to reformulate the sub-query, or use another sub-query within the first sub-query? Am I trying to do too much work in those command statements? I haven't been able to get insight from other similar-looking questions, because they incorporate joins, whereas I am just processing and inserting from one single table to another. I don't have any
WHERE clause or
GROUP BY, and I could group by time, but it shouldn't eliminate any duplicates and would just be for the purpose of using "time" in an aggregate function. On the other hand,
select max("time") - min("time") from time_raw;
gives me an answer of "20:23:33.159866", which I can use to get an integer. So I know that the aggregate functions apply to this data (as it clearly has maximum and minimum timestamp values), I just don't know how to properly formulate the query to get the data I want.