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:

  CAST (time_converted AS bigint) AS "time",
    EXTRACT(EPOCH FROM ("time" - MIN("time"))) * 1000000 AS time_converted
  ) 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, 2016 at 21:29

1 Answer 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, 2016 at 4:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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