Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Guys,
I have an API extracted table which has a column with values similar to this 2016-05-03 10:57:54+12:00. This is text as i tested it using istext() which returned '-1'
I am trying to convert data in this column from Text to Timestamp, so i can split this later into Date and time respectively
I attempted TimeStamp#(MaxString(<field_Name>,1) ) but the result is still the same "Text" value. it does not return value in 'TimeStamp' Type.
Not sure what i am doing wrong. my objective is get column with values like 2016-05-03 10:57:54+12:00 to be converted into regular timestamp of local type and use it to split them into Date and time respectively.
Hope this make sense. Any help will be hugely appreciated.
Thanks.
try this
Date(floor(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY') as Date
Time(frac(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss'),1/24),'hh:mm:ss') as Time
Hello Pranav,
i have tested this with following script and it works fine.
try to add the Format to the timestamp function:
Timestamp#(MaxString(<field_Name>,1), 'DD-MMM-YYYY hh:mm:ss')
testscript:
time:
Load
Timestamp#('14-JUN-2015 17:14:26', 'DD-MMM-YYYY hh:mm:ss') as time
AutoGenerate 1;
transform:
Load
date(time) as date,
time(time) as time,
day(time) as day,
month(time) as month,
year(time) as year
resident time;
try this
Date(floor(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss')),'DD-MM-YYYY') as Date
Time(frac(TimeStamp#(subfield(FieldName,'+',1),'YYYY-MM-DD hh:mm:ss'),1/24),'hh:mm:ss') as Time
or may be like this:
LOAD *,
Date(Floor(TimeStampField)) as DateField,
Time(Frac(TimeStampField)) as TimeField;
LOAD *,
TimeStamp(TimeStamp#(Left(FieldName, 19), 'YYYY-MM-DD hh:mm:ss')) as TimeStampField
FROM Source;
Thank you very much guys for taking the time out and replying.
All the suggestions provided worked. Have marked the very first reply to this question from Kaushal as correct answer.
Cheers,
Pranav