CODEWITHSUNDEEP
sqlnetezza
Alankar
Alankar

Reputation: 197

Time Difference in Seconds using Netezza?

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

Upvotes: 4

Views: 23992

Answers (5)

Muthukumar Ramamurthy
Muthukumar Ramamurthy

Reputation: 31

I suggest to use Netezza SQL Extension Toolkit:

select seconds_between('1996-02-27 06:12:33','1996-02-27 06:55:22');

See Date and Time function reference.

Upvotes: 3

ScottMcG
ScottMcG

Reputation: 3887

I recommend using an expression like the following if you want a signed result of seconds between the two timestamps.

TESTDB.ADMIN(ADMIN)=> select * from time_diff;
        COL1         |        COL2
---------------------+---------------------
 2014-06-24 21:28:33 | 2014-06-22 21:30:58
(1 row)

TESTDB.ADMIN(ADMIN)=> select extract(epoch from col1) - extract(epoch from col2) diff from time_diff;
  DIFF
--------
 172655
(1 row)

TESTDB.ADMIN(ADMIN)=> select extract(epoch from col2) - extract(epoch from col1) diff from time_diff;
  DIFF
---------
 -172655
(1 row)

If you aren't interested in a signed result then using the seconds_between function provided by the SQL Extension Toolkit as mentioned by mc110 works well, or you could simply apply the ABS function to the first solution.

If you have the SQL Extension Toolkit installed in your current database you wont need the SQLEXT.. qualifier I've used here. I happen to have the toolkit installed in a separate database I've called SQLEXT.

TESTDB.ADMIN(ADMIN)=> select abs(extract(epoch from col2) - extract(epoch from col1)) diff from time_diff;
  DIFF
--------
 172655
(1 row)

TESTDB.ADMIN(ADMIN)=> TESTDB.sqlext..seconds_between(col1,col2) diff from time_diff;
  DIFF
--------
 172655
(1 row)

I recommend against using extract with "seconds" as the result you get probably won't be what you intended. Similarly using a single extract with "epoch" around a subtraction of the two values values may also not give you what you expect.

TESTDB.ADMIN(ADMIN)=> select extract(seconds from col1 - col2) diff from time_diff;
 DIFF
------
   35
(1 row)

TESTDB.ADMIN(ADMIN)=> select extract(epoch from col1 - col2) diff from time_diff;
  DIFF
--------
 172655
(1 row)

TESTDB.ADMIN(ADMIN)=> select extract(epoch from col2 - col1) diff from time_diff;
  DIFF
---------
 -172654
(1 row)

Upvotes: 1

mc110
mc110

Reputation: 2833

https://www-304.ibm.com/connections/forums/html/topic?id=1f1f9bec-9ed4-42f4-9242-c099a8120523 suggests

extract(epoch from (timestamp2 - timestamp1))

and one of the comments indicates an alternative:

If you install the Netezza SQL Extension Toolkit you can simply use the seconds_between function, e.g. seconds_between(timestamp2, timestamp1).

Upvotes: 2

comfortablydrei
comfortablydrei

Reputation: 316

Alternative to Alankar:

Select 
    R.*,S.*,CM.URL,
    EXTRACT(SECOND 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

Upvotes: 2

Alankar
Alankar

Reputation: 197

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

Upvotes: 6

Related Questions