Skip to main content

CASE statement gives different output for 11/03/2013 DST change date

  • November 13, 2013
  • 4 replies
  • 26 views
ramesh_1
Forum|alt.badge.img
Hi Support,
Can you please tell me why I am getting different output from these queries.I know the flightStartDate is not a TimestampTZ field in first table, but the CASE statement doesnt goto ELSE part in both these query.


drop table TimeZoneTestTable;
create  table TimeZoneTestTable (adId int, flightStartDate timestamp);
insert into TimeZoneTestTable values(1,'2013-10-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-11-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-02 13:00:00');
insert into TimeZoneTestTable values(1,'2014-11-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-22 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-12 13:00:00');
select case when to_timestamptz(1383451200) at timezone 'EST5EDT' > min(flightStartDate) at timezone 'EST5EDT' then to_timestamptz(1383451200) at timezone 'EST5EDT' else min(flightStartDate) at timezone 'EST5EDT' end as startDate
 from TimeZoneTestTableTimeZoneTestTable
group by adId;

drop table TimeZoneTestTable;
create  table TimeZoneTestTable (adId int, flightStartDate timestamptz);
insert into TimeZoneTestTable values(1,'2013-10-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-11-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-02 13:00:00');
insert into TimeZoneTestTable values(1,'2014-11-02 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-22 13:00:00');
insert into TimeZoneTestTable values(1,'2013-12-12 13:00:00');
select case when to_timestamptz(1383451200) at timezone 'EST5EDT' > min(flightStartDate) at timezone 'EST5EDT' then to_timestamptz(1383451200) at timezone 'EST5EDT' else min(flightStartDate) at timezone 'EST5EDT' end as startDate 
from TimeZoneTestTable
group by adId;



Thanks
Ramesh


    4 replies

    ramesh_1
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • November 20, 2013
    Hi Support,

    Any update on this ?

    Thanks
    Ramesh.
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • November 21, 2013
    Hi Ramesh,

    I don't know the answer to this offhand...  But you've reached the user forums, not Support.  If you want to contact Support, I believe you can do so through http://my.vertica.com/ using your Enterprise account.

    If I had to guess, I would say that min(flightStartDate) has to be a TimestampTz in order to perform the comparison, meaning that you're getting a cast using a default timezone (rather than 'EST5EDT') in the first case.  That's only a guess, but it's easy to check -- try explicitly converting it to a TimestampTz in the timezone that you want.

    Adam
    ramesh_1
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • November 21, 2013
    Thank you Adam. Yeah I tried by converting the flightStartDate to timestamptz it returns same output now.

    Thanks
    Ramesh.
    Daniel_Leybovic
    Forum|alt.badge.img+1
    • Daniel_Leybovic
    • Participating Frequently
    • November 22, 2013
    daniel=> \p
    SELECT CASE 
     WHEN to_timestamptz(1383451200.0) at timezone 'EST5EDT' > min(flightStartDate) at timezone 'EST5EDT'
      THEN to_timestamptz(1383451200.0) at timezone 'EST5EDT'
     ELSE min(flightStartDate) at timezone 'EST5EDT'
     END AS startDate
    FROM TimeZoneTestTable
    GROUP BY adId;

    daniel=> \g
           startDate        
    ------------------------
     2013-11-03 00:00:00+02
    (1 row)

    --------------------------------------------
    daniel=> \p
    SELECT CASE 
     WHEN to_timestamptz(1383451200)::TIMESTAMP at timezone 'EST5EDT' > min(flightStartDate) at timezone 'EST5EDT'
      THEN to_timestamptz(1383451200)::TIMESTAMP at timezone 'EST5EDT'
     ELSE min(flightStartDate) at timezone 'EST5EDT'
     END AS startDate
    FROM TimeZoneTestTable
    GROUP BY adId;

    daniel=> \g
           startDate        
    ------------------------
     2013-11-03 13:00:00+02
    (1 row)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement