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.