Partition Expression

Question

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)))

Jim_Knicely · Answer

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 TABLEdbadmin=> 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)

Dates

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: