Could someone suggest me on the usage of "TIMESTAMP WITH TIME ZONE" column?
One of our tables is partitioned on "TIMESTAMP WITH TIME ZONE" column, ranged partitioned as
PARTITION BY RANGE_N(CAST((TestCol1 ) AS DATE AT TIME ZONE 0 ) BETWEEN DATE '2012-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ,
NO RANGE, UNKNOWN);
When I query the table to see the data on particular day (for example on 1st Oct 2013), I am doing like this..
WHERE TestCol1 between timestamp '2013-10-01 00:00:00' and timestamp '2013-10-01 23:59:59';
With this I am seeing the event timestamps that occured not only on 1st Oct 2013, but also a day before and after.
This is because, all the user event timestamps across the world are normalized w.r.t my Teradata System's time zone.
But I don't want this normalization to be done, and interested in seeing the actual user local timestamps that occured on 10/1/2013
Meaning, in the above example I don't expect to see 10/2/2013 01:08:00+04:00; 10/2/2013 01:08:00+03:00
1. cast(cast(TestCol1 as char(10)) as date) = date '2013-10-01'. This works, but is not recommended because it will not utilize the range partition index defined on the column. It's a very huge table, and full table scan should be avoided.
2. set session zone user;
With this, I think the timestamps will again be normalized to user defined time zone interval.
Is there a way to avoid this normalization and get the actual user local event timestamp, without impacting the performance of the system?
Any suggestions please.
A bit of ugly but it shoud use the 3-4 partitons only
WHERE TestCol1 between timestamp '2013-09-30 00:00:00' and timestamp '2013-10-02 23:59:59'
and and cast(TestCol1 as char(10)) = date;