0

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.

  • I know that I could probably break the conversion down step by step and load the output of each step into intermediary tables. But I would highly prefer the work to be done in a single query, because my supervisor told me to try to minimize the number of intermediate steps, so it would be best for me to try to follow his guidelines. – Ben Jul 15 '16 at 21:29
1

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
...
  • I already had time_pyramid set up, but I definitely needed the window function to achieve my goal. Also, I would not have picked up that the window function needs to be associated with the min(), so that was definitely helpful, thanks! – Ben Jul 16 '16 at 4:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.