All Forums General
kooper 7 posts Joined 10/12
01 Oct 2013
Oracle to Teradata SQL equivalents?

Hi all

First post. Apologies in advance if this question has been asked to death before and/ or if I'm posting this to the wrong section.

Bit of background (feel free to skip this part if you don't feel like reading):
I'm responsible for deploying monitoring for various different systems, the latest of which uses a Teradata DB. I'm used to writing and tweaking Oracle SQL scripts, but not at all when it comes to Teradata SQL.

The questions:
I'm looking for a Teradata equivalent to the following Oracle SQL:
Select datecol, someothercol from sometable where datecol >= sysdate - 1/24
In other words, select everything where the datecol values are bigger or equal to one hour ago.

I've googled this quite a lot, but so far the examples I've stumbled across seem to suggest that a date of some sort must be entered each time. I can probably find a way to do this via Python scripting, but hoping for a SQL method to do this.

Then while I'm at it, does anyone perhaps know of an Oracle/ Teradata cheatsheet of some sort? This isn't crucial of course, but just thought I'd ask anyway in the hopes that someone might know of something off the top of their head.

Thanks in advance

CarlosAL 512 posts Joined 04/08
01 Oct 2013

Derrick:
In Teradata DATES are DATES and TIMES are TIMES and TIMESTAMPS are TIMESTAMPS. Forget the Oracle nonsense of using DATES that are actually datetimes.
SELECT * FROM YOUR_TABLE WHERE YOUR TIMESTAMP_COLUMN >= CURRENT_TIMESTAMP(0) - INTERVAL '1' HOUR is what you are after.
HTH.
Cheers.
Carlos.
 

M.Saeed Khurram 544 posts Joined 09/12
01 Oct 2013

You can use the following:

 

SELECT CURRENT_TIMESTAMP(0) - INTERVAL  '1' HOUR;

You can not subtract HOUR from date, thats why you will have to either use timestamp, Or convert the date to timstamp format 
like '2013-10-01 00:00:00'
 

Khurram

kooper 7 posts Joined 10/12
02 Oct 2013

Hi CarlosAL, M.Saeed Khurram
 
Many thanks for the help so far.
Not to flesh out my original question too much, but is there any way of concatenating the date and time columns and somehow converting that to a timezone column? The structure of the table in question:
 

Column Name

Type

Comment

Nullable

Format

Title

Max Length

Decimal Total Digits

Decimal Fractional Digits

Range Low

Error_Date

DA

null

Y

YYYY-MM-DD

null

4

null

null

null

Error_Time

TZ

null

Y

HH:MI:SSZ

null

14

null

0

null

Error_Message

CV

null

Y

X(8000)

null

16000

null

null

null

 
Sorry for again harping on about Oracle, but the query I'd have likely used for an Oracle DB would've been something like this:
select * from table where to_date(Error_Date || ' ' || Error_Time, 'YYYY-MM-DD HH24:MI:SS') > sysdate - 1/24
 
Here's what I've tried to do for Teradata:
select * from table where CAST(CAST(Error_Date AS CHAR(10)) || ' ' || CAST(Error_Time AS CHAR(8)) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DD HH:MI:SS') > CURRENT_TIMESTAMP(0) - INTERVAL '1' HOUR
select * from table where CAST(CAST(Error_Date AS CHAR(10)) || ' ' || CAST(Error_Time AS CHAR(8)) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DD HH:MI:SSZ') > CURRENT_TIMESTAMP(0) - INTERVAL '1' HOUR
 
Pretty sure I'm doing something silly though as the above queries both result in: [Error 6760] [SQLState HY000] Invalid timestamp.
 
 
I'm googling this as we speak, but if you could perhaps spot something obvious that I'm doing wrong, please feel free to put me out of my misery.
 
Thanks again.

kooper 7 posts Joined 10/12
02 Oct 2013

Sorry about my previous post's mess, tried to copy and paste a table. The structure of the Teradata table again:
 
Column Name: Error_Date
Type: DA
Comment: null
Nullable: Y
Format: YYYY-MM-DD
Title: null
Max Length: 4

Column Name: Error_Time
Type: TZ
Comment: null
Nullable: Y
Format: HH:MI:SSZ
Title: null
Max Length: 14

dnoeth 4628 posts Joined 11/04
02 Oct 2013

It's just a minor mistake: Within the FORMAT you can't use ' ' for blanks you must use 'B' -> FORMAT 'YYYY-MM-DDbHH:MI:SS'
But if your error_time includes a time zone you migth get a different result when you exclude it, so this might be better:

 CAST(CAST(Error_Date AS CHAR(10)) || ' ' || CAST(Error_Time AS CHAR(14)) AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDbHH:MI:SSZ')

And without those expensive typecasts to/from strings:

CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND)

Both will adust the time zone to your default time zone, but you have to check if #2 actually returns the same as #1, your system might have some other default settings (one of the reasons why most people try to avoid time zones).
 
Dieter
 

Dieter

M.Saeed Khurram 544 posts Joined 09/12
02 Oct 2013

Hi,
You are very close to the conversion. you can convert the two columns using the following format:

SELECT CAST(
CAST('2013-10-02' AS VARCHAR(10)) ||' '||CAST('02:41:30 +00:00' AS VARCHAR(15)) AS TIMESTAMP(0) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SSBZ') ;

 

Khurram

kooper 7 posts Joined 10/12
02 Oct 2013

Many thanks for the quick help again, dnoeth and M.Saeed Khurram.
My resultant query seems to now be working like a charm. I'll definitely be bookmarking this page for future reference :)
 
Thanks again for the help, appreciate it.

M.Saeed Khurram 544 posts Joined 09/12
02 Oct 2013

Great! You can postback the working query as a help for others :)

Khurram

kooper 7 posts Joined 10/12
03 Oct 2013

Good point, current SQL query we have in place looks as follows:

select CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND), Error_Message from table where CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND) > CURRENT_TIMESTAMP(0) - INTERVAL '1' HOUR order by CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND) desc

 

dnoeth 4628 posts Joined 11/04
03 Oct 2013

In Teradata you can re-use an alias instead of cut&paste to further simplify to 

select CAST(error_date AS TIMESTAMP(0)) + (error_time - (TIME '00:00:00') HOUR TO SECOND) AS ErrorTS,
   Error_Message 
from table 
where ErrorTS > CURRENT_TIMESTAMP(0) - INTERVAL '1' HOUR 
order by ErrorTS desc

 
Dieter

Dieter

kooper 7 posts Joined 10/12
04 Oct 2013

Ah nice one, thanks for that Dieter

You must sign in to leave a comment.