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
Moving 'at timezone' to the right side
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Moving 'at timezone' to the right side
Hi,
What is the data type of the visit_start_time_15m column? Is there a TZ in there?
What is the data type of the visit_start_time_15m column? Is there a TZ in there?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Moving 'at timezone' to the right side
Hi,
Yep it's TZ
vladi
Yep it's TZ
vladi
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Moving 'at timezone' to the right side
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Moving 'at timezone' to the right side
The timezone is : Israel
Not sure I understand. Why it's important?
Not sure I understand. Why it's important?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Moving 'at timezone' to the right side
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:
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.
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)
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.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Moving 'at timezone' to the right side
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
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