Postgresql – Convert timestamp type during INSERT

insertpostgresqltype conversionwindow functions

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.

Best Answer

Basically, you need a window function instead of the aggregation. Appending an OVER clause to an aggregate function makes it a window-aggregate function:

SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000, 1
FROM   time_raw
GROUP  BY "time";

And since you want the minimum of the whole table, it's OVER () (nothing between the parentheses).

And you don't need a subquery. Fewer steps, right?

But what do you mean by "time_pyramid will have 3 columns".

Do you mean to create the table? Then use CREATE TABLE AS and apply aliases aliases to all columns in query:

CREATE TABLE time_pyramid AS
SELECT 0 AS layer_number
     , EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000 AS time_converted
     , 1 AS count_number
...

Or that's just a typo and you mean to insert into an existing table. Then append target columns to make your INSERT safer (or valid to begin with):

INSERT INTO time_pyramid (layer_number, time_converted, count_number)
SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") <b>OVER ()</b>))::bigint * 1000000, 1
...
Related Question