Custom Aggregation Filter

Comments

1 comment

  • Avatar
    Marcin Daczkowski

    Hey looks to me semi-monthly is a bit off. Seems to start attribute any day 12 or greater to second part on month. Also tht particular part lacks ::timestamp cast which may come problematic sometimes. 

    I came up with the following adjustment (basically following same sqrt idea as in original version, but adjusting parameters):

    SELECT (data)::timestamp, TO_DATE(date_part(year, (data)::timestamp) || '-' || date_part(month, (data)::timestamp) || '-' || floor(sqrt((date_part(day, (data)::timestamp)::numeric-1)/5+1)) * 15 - 14, 'YYYY-MM-DD')
    FROM
    (SELECT '2020-03-01' as data
    UNION ALL
    SELECT '2020-03-02' as data
    UNION ALL
    SELECT '2020-03-03' as data
    UNION ALL
    SELECT '2020-03-04' as data
    UNION ALL
    SELECT '2020-03-05' as data
    UNION ALL
    SELECT '2020-03-06' as data
    UNION ALL
    SELECT '2020-03-07' as data
    UNION ALL
    SELECT '2020-03-08' as data
    UNION ALL
    SELECT '2020-03-09' as data
    UNION ALL
    SELECT '2020-03-10' as data
    UNION ALL
    SELECT '2020-03-11' as data
    UNION ALL
    SELECT '2020-03-12' as data
    UNION ALL
    SELECT '2020-03-13' as data
    UNION ALL
    SELECT '2020-03-14' as data
    UNION ALL
    SELECT '2020-03-15' as data
    UNION ALL
    SELECT '2020-03-16' as data
    UNION ALL
    SELECT '2020-03-17' as data
    UNION ALL
    SELECT '2020-03-18' as data
    UNION ALL
    SELECT '2020-03-19' as data
    UNION ALL
    SELECT '2020-03-20' as data
    UNION ALL
    SELECT '2020-03-21' as data
    UNION ALL
    SELECT '2020-03-22' as data
    UNION ALL
    SELECT '2020-03-23' as data
    UNION ALL
    SELECT '2020-03-24' as data
    UNION ALL
    SELECT '2020-03-25' as data
    UNION ALL
    SELECT '2020-03-26' as data
    UNION ALL
    SELECT '2020-03-27' as data
    UNION ALL
    SELECT '2020-03-28' as data
    UNION ALL
    SELECT '2020-03-29' as data
    UNION ALL
    SELECT '2020-03-30' as data
    UNION ALL
    SELECT '2020-03-31' as data
    )

     

    Results:

    0
    Comment actions Permalink

Please sign in to leave a comment.