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;