All Forums Database
depook 6 posts Joined 05/14
05 Apr 2015
Need help on converting timestamp to date and integer column

Hi ,
create table db_name.test
(Row_Date DATE FORMAT 'YYYY-MM-DD',
Starttime INTEGER );
Input as timestamp :
2013-09-11 15:20:00
2012-05-21  08:30:00
2012-03-19  04:00:00
2012-07-07  16:25:00
2014-03-19  00:30:00
2014-08-19  00:35:00
2014-08-19  00:00:00
 
Output will be in two columns as below:
row_date(date)    starttime(integer)
2013-09-11            1520       
2012-05-21            830        
2012-03-19            400
2012-07-07            1625
2014-03-19            30
2014-08-19            35
2014-08-19            0
 
Can you please help me in this ?
 
Thanks in advance,
Deepak

dnoeth 4628 posts Joined 11/04
06 Apr 2015

Hi Deepak,
just reverse Carlos' logic :-)

select 
   cast(ts as date)
  ,extract(hour from ts) * 100 + extract(minute from ts)
from tab

 

Dieter

You must sign in to leave a comment.