0

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?

SELCET date, value 
FROM table 
WHERE date BETWEEN 2022-01-01 AND 2022-02-31

You know that the query works fine when use the first day of the next month:

SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-03-01

My question is how we can handle such a situation on the database side instead of the App side. I mean we can prevent such values from getting into the database inside our App. However, it will be handier if we can fix them inside queries. For example, I seek a procedure that converts invalid dates to the maximum acceptable date: 2022-02-31 --> 2022-02-28

3
  • Your second solution will return something different than the first one because the second query would include rows from 2022-03-01. It should be: WHERE date >= date '2022-01-01' AND date < date '2022-03-01'
    – user1822
    Mar 8, 2022 at 8:23
  • @a_horse_with_no_name yes, you're right; I wanna say the second one returns no error.
    – sci9
    Mar 8, 2022 at 9:18
  • Do you always query for a "month interval"? Then why not specify only the start date and calculate the end date based on that? where date >= ? and date < ? + interval '2 month' (assuming that ? is e.g. 2022-02-01)
    – user1822
    Mar 8, 2022 at 9:59

2 Answers 2

1

Use date arithmetic to calculate the last day of a month. For the current month, that would be:

SELECT CAST(date_trunc('month', current_timestamp) + INTERVAL '1 month' AS date) - 1;
3
  • thank you for your answer, but it still treats more wisely! CAST(date_trunc('month', '2022-02-29'::timestamp) + INTERVAL '1 month' AS date) - 1 returns out of range error.
    – sci9
    Mar 8, 2022 at 14:26
  • Then don't input such dates. Where do they come from? Mar 8, 2022 at 14:48
  • From the Public World! Yes, I can handle them in the upper layer, but I thought there was another approach to tackle them inside the query.
    – sci9
    Mar 8, 2022 at 14:54
-2
select c.bdate,
    case
        when length(c.dot_three)=4 and (c.dot_one::int between 13 and 31) and c.dot_two::int<=12 then concat(c.dot_three,'-',c.dot_two,'-',c.dot_one)
        when length(c.dot_three)=4 and c.dot_two::int<=12 and c.dot_one::int<=12 then concat(c.dot_three,'-',c.dot_two,'-',c.dot_one)
        when length(c.dot_three)=4 and (c.dot_two::int between 13 and 31) and c.dot_one::int<=12 then concat(c.dot_three,'-',c.dot_one,'-',c.dot_two)
        when length(c.dot_one)=4 and (c.dot_three::int between 13 and 31) and c.dot_two::int<=12 then concat(c.dot_one,'-',c.dot_two,'-',c.dot_three)
        when length(c.dot_one)=4 and c.dot_two::int<=12 and c.dot_one::int<=12 then concat(c.dot_one,'-',c.dot_two,'-',c.dot_three)
        when length(c.dot_one)=4 and (c.dot_two::int between 13 and 31) and c.dot_three::int<=12 then concat(c.dot_one,'-',c.dot_three,'-',c.dot_two)
        when length(c.slash_three)=4 and (c.slash_one::int between 13 and 31) and c.slash_two::int<=12 then concat(c.slash_three,'-',c.slash_two,'-',c.slash_one)
        when length(c.slash_three)=4 and c.slash_two::int<=12 and c.slash_one::int<=12 then concat(c.slash_three,'-',c.slash_two,'-',c.slash_one)
        when length(c.slash_three)=4 and (c.slash_two::int between 13 and 31) and c.slash_one::int<=12 then concat(c.slash_three,'-',c.slash_one,'-',c.slash_two)
        when length(c.slash_one)=4 and (c.slash_three::int between 13 and 31) and c.slash_two::int<=12 then concat(c.slash_one,'-',c.slash_two,'-',c.slash_three)
        when length(c.slash_one)=4 and c.slash_two::int<=12 and c.slash_three::int<=12 then concat(c.slash_one,'-',c.slash_two,'-',c.slash_three)
        when length(c.slash_one)=4 and (c.slash_two::int between 13 and 31) and c.slash_three::int<=12 then concat(c.slash_one,'-',c.slash_three,'-',c.slash_two)
        else ''
    end as gdate
from (
    select t.bdate,split_part(bdate,'.',1) as dot_one, split_part(bdate::text, '.',2) as dot_two,
        split_part(bdate::text, '.',3) as dot_three,
        split_part(bdate,'/',1) as slash_one, split_part(bdate::text, '/',2) as slash_two,
        split_part(bdate::text, '/',3) as slash_three
    from
    (SELECT unnest(array['31.08.2014','16.09.2014','13.12.2015','10/9/2018','4/4/2019','2022/4/4','3/2/2018','27/8/2019','15/2/2019','26/11/2019']) as bdate)t
)c
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.