Qualify a Timestamp in Teradata 2

[witchblade]

I am using software called BiQuery with a Teradata platform. The datatype of the timestamps is "timestamp". I need to qualify for a beginning and ending timestamp.

I've tried the following:
{'2001-08-01 00:00:00'}& {'2001-08-02 00:00:00'}
{'2001-08-01 00:00:00:000'}& {'2001-08-02 00:00:00:000'}
{'08/21/2001 00:00:00:000'}& {'08/22/2001 00:00:00:000'}
{'08/21/2001 00:00:00'} & {'08/22/2001 00:00:00'}
08/21/2001 12:00:00 AM & 08/22/2001 12:00:00 AM
{'2001-08-21 12:00:00 AM'} & {'2001-08-22 12:00:00 AM'}
{'08/21/2001 12:00:00 AM'} & {'08/22/2001 12:00:00 AM'}

The format used in Teradata for a regular date field is:
yyyy:mm:dd, but I don't know if that will be the case here, because the sample data shows me:MM/DD/YYYY HH:MM:SS:SSS AM.

Without qualifying, I'll get over 1Million rows.
Help????

 

[jgrogan]

Hi Witchblade

I don't know which version of BI/Query you are using, but I believe that all versions up to and including v6 do not recognize TIMESTAMP as a valid datatype.

Could I suggest you create a view of your table, CASTing the TIMESTAMP column as a CHAR(26), then referencing the view in your BI/Query query? By default, I think TIMESTAMP uses 6 decimal places of seconds (although Teradata doesn't use them all if you write to a TIMESTAMP column). So, this should work (not sure about the '&'...)

...
...rest of query...
...
AND yournewchar26tstamp
BETWEEN {'2001-08-01 00:00:00.000000'}
AND {'2001-08-02 00:00:00.000000'};

Rgds
JG

 

[witchblade]

I'm using Version 7 of BiQuery. How do I CAST the TIMESTAMP column?

 

[jgrogan]

By way of an example, if I had a table based on the following definition:

Create Set Table test_tab
(test_col1   Integer NOT NULL
,test_tstamp Timestamp)
Primary Index (Test_col1);

A view that should work for you in BI/Query might be:

Create View test_tab_view
As
Locking test_tab for access
Select test_col1
      ,CAST(test_tstamp AS CHAR(26)) (NAMED test_tstamp)
From   test_tab;

Then, add the view 'test_tab_view' to your BI/Query model and you should be able to access your data, qualified as you choose.

This assumes you have 'Create View' access on the database you are working in. If not, have a DBA set it up for you.

Rgds
JG

 

[seidub]

continuing from where JG's suggestion, you can also CAST the timestamp as DATE instead of CHAR ... especially if you intend to perform date arithmetic later no:

Create Set Table test_tab
(test_col1 Integer NOT NULL
,test_tstamp Timestamp(0))
Primary Index (Test_col1);


replace view test_tab_view
as lock test_tab for access
select
cast(test_tstamp as date format 'yyyy-mm-dd') as test_tstamp_date,
cast(test_tstamp as time format 'hh:mm:ss') as test_tstamp_time
from
test_tab;

Good luck.

 

[witchblade]

Thanks jgrogan & seidub!