microsoft / azuredatastudio-postgresql

azuredatastudio-postgresql is an extension for Azure Data Studio that enables you to work with PostgreSQL databases
Other
193 stars 36 forks source link

Problem with INTERVAL output #459

Closed LSIND closed 7 months ago

LSIND commented 11 months ago

Describe the bug Using datatype INTERVAL produce incorrect results in the Results grid.

To Reproduce

  1. Run SELECT-statements below which produce INTERVAL datatype as return value:
    
    SELECT '1 year 2 months 20 days 1 hour'::INTERVAL AS i;

SELECT age('20100101'::date, '19940215'::date) AS age_func;

SELECT date_trunc('months', '1 year 2 months 20 days 15 minutes 55 seconds'::interval) as date_trunc_func;

SELECT 5 * '2 years 3 months 5 days'::interval as mult;

2. In the Results window there are only days though it is obvious there should be also years and months in all queries: 
```sql
        i
-------------------
20 days, 1:00:00 

    age_func
-------------------
14 days, 0:00:00

  date_trunc_func
-------------------
60 days, 0:00:00

      mult
-------------------
25 days, 0:00:00

Expected behavior It seems that ADS or extension cuts everything that stands before the days.

As for psql (13.8), it produces correct results:

=# SELECT '1 year 2 months 20 days 1 hour'::INTERVAL AS i;
----------------------------------------------
1 year 2 mons 20 days 01:00:00

=# SELECT age('20100101'::date, '19940215'::date) AS age_func;
----------------------------------------------
15 years 10 mons 14 days

=# SELECT date_trunc('months', '1 year 2 months 20 days 15 minutes 55 seconds'::interval) as date_trunc_func;
----------------------------------------------
1 year 2 mons

=# SELECT 5 * '2 years 3 months 5 days'::interval as mult;
----------------------------------------------
 11 years 3 mons 25 days

Desktop:

ADS:

Additional context Date/timestamp datatypes with arithmetic operator - produce correct (expected) result, for example:

SELECT '20100101'::date - '19940215'::date AS diff1;
        diff1
-------------------
       5799

SELECT '20100101'::timestamp - '19940215'::timestamp AS diff2;
        diff2
-------------------
   5799 days, 0:00:00

P.S. Probably I should also refer to pgtoolsservice.

nasc17 commented 10 months ago

Bug has been addressed in our unstable release of PostgreSQL extension v0.5.1-insiders. We would appreciate your verification that it has been handled appropriately.

LSIND commented 10 months ago

I installed extension v0.5.1 (win x64), the results of interval output are as expected. Thank you for your work!

nasc17 commented 7 months ago

image