Partition Expression

opper20

Hi Team,
i need to write a partition expression based on dates and hour .use case is like this.

Dates

2019-01-01 00:02:22
2019-01-01 03:02:22
2019-01-01 10:02:22
2019-01-01 23:02:22
2019-01-01 15:02:22
2019-02-01 09:02:22
2019-02-01 04:02:22
2019-02-01 23:02:22
Here i have data and hours.My requirement is i need to have 2 partitions for each day where 1st one will hold hours from 00-11 and the other partition for the same day will have 12-23. Similar for the 2nd day as well.
o/p
2019-01-01 (00-11)
2019-01-01 (12-23)
2019-02-01 (00-11)
2019-02-01 (12-23)

We can add suffix also to add at the end of partition so as to identify which partition belong to which hour.
i am using the below syntax but where to add the hour part ,
PARTITION BY ((((date_part('year', time) * 10000) + (date_part('month', time) * 100)) + date_part('day', time)))

opper20

Thanks Jim

Jim_Knicely

Here's one way...

dbadmin=> CREATE TABLE part_test (c TIMESTAMP NOT NULL) PARTITION BY EXTRACT(YEAR FROM c) || EXTRACT(DAY FROM c) || EXTRACT(MONTH FROM c) || CASE WHEN EXTRACT(HOUR FROM c) < 12 THEN 0 ELSE 1 END;;
CREATE TABLE

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 00:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 03:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 10:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 23:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-01-01 15:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 09:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 04:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO part_test SELECT '2019-02-01 23:02:22';
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;

dbadmin=> SELECT do_tm_task('mergeout', 'part_test');
                                                               do_tm_task
-----------------------------------------------------------------------------------------------------------------------------------------
 Task: mergeout
(Table: public.part_test) (Projection: public.part_test_b0)
(Table: public.part_test) (Projection: public.part_test_b1)

(1 row)

dbadmin=> SELECT partition_key, SUM(ros_row_count) ros_row_count FROM partitions WHERE projection_name = 'part_test_b0' GROUP BY 1 ORDER BY 1;
 partition_key | ros_row_count
---------------+---------------
 2019110       |             3
 2019111       |             2
 2019120       |             2
 2019121       |             1
(4 rows)