Thursday, October 7, 2010

Teradata Timestamp tips & tricks - 3


Tip1: Extracting month from date as 'MMM'

select current_date (format 'MMM');

Date
----
Oct

 Please use " sel cast ((cast(current_date as format 'mmm')) as char(3));   "  while querying on Sql Assistant(queryman)


Tip2: Converting number of seconds to Minutes and Seconds

If i want to convert the input of seconds into minutes+seconds,    following query is used
ex: 300 seconds --> 0005:00  or 5:00 mins

create table table1
( second_s integer );
insert into table1 (300);

Select second_s * INTERVAL '0000:01' MINUTE TO SECOND from Table1;

Please note that ,if seconds interval is more then 9999(approx 7 days) , it will probably result in a "7453: Interval field overflow".


Tip3: Converting number of seconds to Minutes and Seconds
Similarly , We can convert seconds to Day to second format as well!!

Select second_s* INTERVAL '0000 00:00:01' DAY TO SECOND from table1;

This will work for up to 27 years  after which it results in "7453: Interval field overflow" error.


Tip4: How to add/subtract minutes to timestamp!!

Problem: To the given timestamp value, how can we add or subtract given minutes.
ex:
 Timestamp1                          Minutes1         Result 
2010-01-04 02:10:08   (+)    330               = 2010-01-04 07:40:08
2010-01-02 18:04:32   (-)     245               = 2010-01-02 13:59:32

create table Table_1
(Timestamp1 timestamp(0) ,Minutes1 integer );
insert into table_1 values('2010-01-04 02:10:08',330);
insert into table_1 values('2010-01-02 18:04:32',245);

select Timestamp1 + (Minutes1 * interval '1' minute) from table_1;
select Timestamp1 - (Minutes1 * interval '1' minute) from table_1;

1 comment:

  1. This post is very useful. I was looking for these calculations since a long time and nothing was as easy as this one. Thanks !!

    ReplyDelete