Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Text toTimestamp# not working

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. 

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

4 Replies
zhadrakas
Specialist II
Specialist II

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;

Kushal_Chawda

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

sunny_talwar

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;

Not applicable
Author

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