Need help with "TIMESTAMP WITH TIME ZONE"

Database

Need help with "TIMESTAMP WITH TIME ZONE"

Hello Developers,

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..

SELECT *

FROM Test_Table1

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.

10/1/2013 17:23:39+03:00

10/1/2013 19:43:36+03:00

10/2/2013 01:08:00+04:00

10/2/2013 01:08:00+03:00

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 

I tried

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.

Many Thanks

Hemanth

2 REPLIES
Supporter

Re: Need help with "TIMESTAMP WITH TIME ZONE"

A bit of ugly but it shoud use the 3-4 partitons only

SELECT *
FROM Test_Table1
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;

Re: Need help with "TIMESTAMP WITH TIME ZONE"

Thanks Ulrich, it works.

I couldn't find any better solution than yours..

Thanks!