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

ramesh_1

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

ramesh_1

Hi Support,

Any update on this ?

Thanks
Ramesh.

[Deleted User]

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

Thank you Adam. Yeah I tried by converting the flightStartDate to timestamptz it returns same output now.

Thanks
Ramesh.

Daniel_Leybovic

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)