Oracle to Teradata SQL equivalents?

General
Enthusiast

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
Tags (3)
11 REPLIES
Junior Supporter

Re: Oracle to Teradata SQL equivalents?

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.

Enthusiast

Re: Oracle to Teradata SQL equivalents?

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
Enthusiast

Re: Oracle to Teradata SQL equivalents?

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.

Enthusiast

Re: Oracle to Teradata SQL equivalents?

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

Senior Apprentice

Re: Oracle to Teradata SQL equivalents?

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

Enthusiast

Re: Oracle to Teradata SQL equivalents?

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
Enthusiast

Re: Oracle to Teradata SQL equivalents?

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.

Enthusiast

Re: Oracle to Teradata SQL equivalents?

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

Khurram
Enthusiast

Re: Oracle to Teradata SQL equivalents?

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