Need help on converting timestamp to date and integer column

Database
Enthusiast

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

Tags (1)
1 REPLY
Junior Contributor

Re: Need help on converting timestamp to date and integer column

Hi Deepak,

just reverse Carlos' logic :-)

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