-1

Suppose I have a table (myTable) of datetime indexed objects:

  dttm                |  price
  ----------------------------------
  '2022-7-11 T00:00'  |   10
  '2022-7-11 T01:00'  |   11
  ...                 |  ...
  '2022-7-11 T23:00'  |   19
  '2022-7-12 T00:00'  |   20
  '2022-7-12 T01:00'  |   21
  ...                 |  ...
  '2022-7-12 T23:00'  |   29
  ...                 |  ...
  '2022-7-27 T00:00'  |   90
  '2022-7-27 T01:00'  |   91
  ...                 |  ...
  '2022-7-27 T23:00'  |   99

I would like to group these by date, then join all the resulting columns by time:

   time     | '2022-7-11'  | '2022-7-12'  | ... | '2022-7-27'
  ------------------------------------------------------------
  'T00:00'  |    10        |      20      | ... |     90
  'T01:00'  |    11        |      21      | ... |     91
  ...       |    ...       |      ...     | ... |     ...
  'T23:00'  |    19        |      29      | ... |     99

If the table was sufficiently small, this could be achieved via code like:

  (SELECT dttm::time, price as '2022-7-11'
     FROM myTable
     WHERE dttm::date='2022-7-11') as d1
  JOIN
  (SELECT dttm::time, price as '2022-7-12'
     FROM myTable
     WHERE dttm::date='2022-7-12') as d2
  JOIN
  ...
  JOIN 
  (SELECT dttm::time, price as '2022-7-27'
     FROM myTable
     WHERE dttm::date='2022-7-27') as d9

But what if the table was not sufficiently small? e.g. 100 days/columns. Sure there is a way to express this within a GROUP BY statement, or some other way?

(I am using Postgres 14.1, server 12.4, but ideally would like answer to comply with SQL-standard)

3
  • 1
    Welcome to dba.se Could you please share why you're trying to achieve this kind of output? As 1. You're not actually grouping anything, 2. You can potentially have infinite columns and rows not just 100 (each day for each year for N years and each second, minute, hour of each day is not a small amount of columns and rows) 3. You're actually trying to pivot, but even with pivoting this makes little sense without more context and explanation.
    – Chessbrain
    Jul 26 at 12:57
  • I am not sure why more context is needed - the question is purely mechanical, about how to achieve the outcome of iterative joining through a SQL expression.
    – Owen
    Jul 27 at 5:06
  • If you want more context though, the Australian electricity industry regularly exports files of timeseries data in a square format (one row for a whole day of data and one column for every 30min or every 5min - the transpose of the above example). It would be useful to be able to export data from the database into a square csv file.
    – Owen
    Jul 27 at 5:13

0

Your Answer

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

Browse other questions tagged or ask your own question.