Partition Expression

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

Best Answer

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited November 2019

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file