Thursday, August 2, 2012

Useful Netezza Date Functions

If you haven't already done so, I highly recommend installing the SQL Extension Toolkit package on your Netezza box. Upon installation, you will have access to many very useful additional functions to make your life easier when writing sql.

Here are some useful date functions I tend to use:

--first of this year
select date_trunc('year', CURRENT_DATE)

--how to get first of the month given a date
select date_trunc('month', current_timestamp)

--how to display monthname-YYYY format - ie December-2012
select trim(to_char(now(),'Month')) || '-' || year(current_date);

-how to display YYYY-MM format - ie 2012-12

select TO_CHAR(current_date,'YYYY-MM');


--truncate timestamp given a date
select date(current_timestamp)--this does not require SQL Extension Toolkit
or
select date_trunc('day', current_timestamp) --requires SQL Extension Toolkit

--select next Monday's date based on today's date
select next_day(DATE(current_timestamp), 'Monday') --if your date is a timestamp and you just want the truncated date for next monday
select next_day(current_date, 'Monday')--if your date is a truncated date

--subtract 1 month from today's date
select date(current_date -  cast('1 month' as interval));

--add 6 days  from today's date
select date(current_date +  cast('6 days' as interval))
or
select  current_date - 7

--if you have a timestamp column and want to subtract say 5 days
select date(yourtimestampcolumn) - 5



--Difference in days between 2 dates (if you have sql exension toolkit installed) 
select EXTRACT (DAY FROM DateCol1 -DateCol2) as difference_in_days
 from tableA
 limit 100;
OR
-- epoch returns number of seconds
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/86400 as difference_in_days
 from TableA
limit 100;

--Difference in hours between 2 dates (epoch returns number of seconds)
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/3600 as difference_in_hours
 from TableA
limit 100;


--for those of you who are used to SQL SERVER's datepart function, you are in luck!
select date_part('day', current_date) as day_of_month, date_part('week', current_date) as week_of_year

--Add 1 hour to  a timestamp
select current_timestamp, current_timestamp +  cast('1 hour' as interval)

--Add 1 hour to a date
select TO_CHAR(datetime(date('2013-02-26') +  cast('1 hour' as interval)),'YYYY-MM-DD HH24:MI:SS')

14 comments:

  1. GREAT STUFF, helped me out big time :)

    ReplyDelete
  2. Thanks for the efforts on gathering useful content and sharing here. You can find more question and answers on Netezza database in the following forum.

    Netezza database question and answers

    ReplyDelete
  3. Great... thanks!!

    ReplyDelete
  4. Thankyou so much you saved my time.

    ReplyDelete
  5. Good post. I wish the Netezza user guide had some good examples. Very helpful, thanks to you and google :)

    ReplyDelete
  6. Thanks! You saved my day!
    Its a great collection on Netezza Database. Can you create a post on difference between DB2 Database and Netezza Database? It would be very useful one!

    ReplyDelete
  7. thanks, same input as previous, you saved my day. this a great page with examples.

    ReplyDelete
  8. Thank you so much...

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. I need to get the name of the day from weekdate in netezza. For eg. 04/25/2014 should return me friday. any suggestions?

    ReplyDelete
  11. Difference between two time-stamp in should be in the format of decimal value.
    Netezza Database
    Eg:
    select TO_TIMESTAMP('2014-02-19 12:00:00','yyyy-mm-dd hh24:mi:ss') - TO_TIMESTAMP('2014-02-03 00:00:00','yyyy-mm-dd hh24:mi:ss')

    Current Output: 16 days 12:00:00
    Expected output; 16.5

    Please help me out.

    ReplyDelete
    Replies
    1. select CAST(EXTRACT(EPOCH FROM TO_TIMESTAMP('2014-02-19 12:00:00','yyyy-mm-dd hh24:mi:ss') - TO_TIMESTAMP('2014-02-03 00:00:00','yyyy-mm-dd hh24:mi:ss')) AS FLOAT) / (60*60*24);

      Delete
  12. Wonderful information, it is very helpful, thank you. Does anyone know if something like this exists out there for working with the time portion of a time stamp. I.E. spliting off hour as an attribute.

    ReplyDelete
  13. thanks a lot for the information.highly helpful to me to calculate the previous year by providing the following parameter
    select date(date('29/05/2013') - cast('1 year' as interval));

    ReplyDelete