Moving 'at timezone' to the right side

[vladif86]

Hi All,

Please help us to resolve the following doubt: Are the these two line equivalent ?

1. ....and visit_start_time_15m at timezone 'GMT' >= to_timestamp('01/01/2013','dd/mm/yyyy')

2. ....and visit_start_time_15m >= to_timestamp('01/08/2013','dd/mm/yyyy') at timezone 'GMT'

The first line causes full table scan because of at timezone function so we're thinking to go with the option #2.
The queries we've tested return the same result comparing to first option but we can't explain it!
If they are equivalent how does it work?
Thank you,
VladiF

[JimKnicely]

Hi,

What is the data type of the visit_start_time_15m column? Is there a TZ in there?

[vladif86]

Hi,
Yep it's TZ
vladi

[JimKnicely]

What is the TZ of the session and default TZ for the DB? Mine is EST.

Code: Select all

dbadmin=> show timezone;
   name   |  setting
----------+------------
 timezone | US/Eastern
(1 row)

[vladif86]

The timezone is : Israel
Not sure I understand. Why it's important?

[JimKnicely]

Hi,

The TIMESTAMPZ data type is "a 64-bit integer in little-endian format containing the number of microseconds since Julian day: Jan 01 2000 00:00:00 in the UTC timezone". I believe that if you declare a table column as a TIME STAMP WITH TIME ZONE the data is is always stored in UTC time.

When you are comparing a timestamp to a timestampz, I think Vertica casts the timestamp to timestampz by appending the UTC offset.

Example:

Code: Select all

dbadmin=> create table tz_test (t1 timestamp, t2 timestamp with time zone);
CREATE TABLE
dbadmin=> set timezone Israel;
SET
dbadmin=> insert into tz_test values ('2013-10-21'::timestamp, '2013-10-21'::timestamp with time zone);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from tz_test where t1 = t2;
         t1          |           t2
---------------------+------------------------
 2013-10-21 00:00:00 | 2013-10-21 00:00:00+02
(1 row)

See how they are equal?

in your second example, I don't think you need:

....and visit_start_time_15m >= to_timestamp('01/08/2013','dd/mm/yyyy') at timezone 'GMT'

you can just put:

....and visit_start_time_15m >= to_timestamp('01/08/2013','dd/mm/yyyy')

And you'll be comparing the same time zones ...

Anyway, time zone crap always confuses me but I hope I helped a little here.

[vladif86]

Thank you very much for the clarifications
But (there is always this BUT !!)
We insert our data in UTC format into TZ columns, then when selecting the data we want to take into account the client timezone . So per request/session we have to set (somehow) the client timezone
That why we're thinking to add : AT TIMEZONE 'XYZ' on the TZ columns
But when using it with the column name we cause the Vertica full table scan so that why we're thinking moving it to the right side (to the parameters value) will resolve the full table scan and adjust the time to the required by the timezone (not UTC)
Thank you,
VladiF