Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Firstly thanks for any assistance you can provide.
I have a simple problem (I think it should be) and get resolve it.
I am trying to create a timestamp field that combines the DATE eg. (20/05/2011 00:00:00) with the HOUR (currently a number to represent) and the MINUTE (same format as HOUR)
I have run into a couple of issues.
Firstly I have tried to combine HOUR and MINUTE into TIME - using
time#(num(HOUR&MINUTE), 'hhmm') as TIME
if(Len(TIME)=4 ,Time(Time#(TIME,'hhmm'),'hh:mm') , if(Len(TIME)=3 ,Time(Time#('0'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=2 ,Time(Time#('00'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=1 ,Time(Time#('000'&TIME,'hhmm'),'hh:mm') , 0 ) ) ) )
to format the TIME correctly, but this is causing problems with MINUTES starting with a 0.
I have used the Floor(DATE) function to realise the date component of the timestamp, but I am at a lose and can not seem to find help on effectively concatonating values together to form a timestamp.
Thanks again
try timestamp(floor(DATE) + HOUR/24+MINUTE/1440)
If DATE is already interpreted as date / timestamp with a numerical representation (you can check this in table view by hovering over the field name), I think it could look like
LOAD
DATE,
HOUR,
MINUTE,
Timestamp(DATE + maketime(HOUR, MINUTE)) as TIMESTAMP
FROM ....;