Time Difference in Seconds using Netezza?

sql netezza
Alankar picture Alankar · Jun 4, 2014 · Viewed 21.3k times · Source

I want to Get time difference in minutes in Netezza in Seconds.

I have 2 columns: Start(2014-06-01 07:45:04) and finish(2014-06-01 07:46:58) events

I have tried Using DATEDIFF(SECOND,start,finish), but Netezza shoots an error.

Is there a function I am missing?

Select R.*,S.*,CM.URL,DATEDIFF(SECOND,R.EVENT_DTM,S.MIN) From CTE_1 R 
JOIN CTE_2 S
on S.VISIT_KEY = R.VISIT_KEY
JOIN ADMIN.VW_DIM_CME_METADATA CM on CM.CONTENT_METADATA_KEY = R.CONTENT_METADATA_KEY

Answer

Alankar picture Alankar · Jun 4, 2014

Did some Searching on the IBM forums and found this which works. If someone comes with better way please do post it. Thanks

    Select 
    R.*,S.*,CM.URL,
    EXTRACT(EPOCH from S.MIN - R.EVENT_DTM)DIFF 
    From CTE_1 R 
    JOIN CTE_2 S
    on S.VISIT_KEY = R.VISIT_KEY
    JOIN ADMIN.VW_DIM_CME_METADATA CM on CM.CONTENT_METADATA_KEY = R.CONTENT_METADATA_KEY

Related questions

How to delete duplicate rows without unique identifier

I have duplicate rows in my table and I want to delete duplicates in the most efficient way since the table is big. After some research, I have come up with this query: WITH TempEmp AS ( SELECT name, ROW_NUMBER() …

Read More
Netezza SQL syntax to convert numeric YYYYMMDD format into date

We have a Netezza table that contains dates stored in a numeric YYYYMMDD format (eg 20090731). What is the best Netezza syntax to use to convert this into date format? eg SELECT somefunction(20090731) as NZDATE ?

Read More
How do I list all the column names in Netezza?

Is there a query I can write to search all the column names for a particular database in Netezza?

Read More