Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ivanaksen
New Contributor III

Time formating

Hello Guys,

Please support with the bellow question:

I have such format of date. It contains both date and time(YYYYMMDDHHMMSS).


1.jpg

But I have problem in converting into more applicable format. For Date I found resolution in such command:
Date(Date#(left(APP_03,8),'YYYYMMDD'),'DD-MM-YYYY'). Usual DATE command showed me 01.01.9999.

But with time I have problem. When I use the same logic: 
time(time#(right(APP_03,6),'hhmmss'),'HH:MM:SS'), Insted of 12:19:50, I have 11:53:16. So just randomly incorrect time.
When I am using only time#(right(APP_03,6),'hhmmss'), I have correct time 121950, but when I am trying to add separators ":", I have this wrong time. 

Also can you advise, how can i write the command to add for all cells with time, 3 hours. In this export I have UTC±00:00 for all data, But it`s necessary to have UTC+03:00

Thanks in advance

Tags (1)
Labels (1)
2 Solutions

Accepted Solutions
Partner
Partner

Re: Time formating

My bad !! I havn't delete the ,6 part related to ur left funtion !

 

Try:

Date( timestamp#(APP_03,'YYYYMMDDhhmmss'), 'DD-MM-YYYY') as DateField

Time( timestamp#(APP_03,'YYYYMMDDhhmmss'), 'hh:mm:ss') as TimeField

Partner
Partner

Re: Time formating

Maybe sthing like :

Time( timestamp#(APP_03,'YYYYMMDDhhmmss') + time#(03,'hh'), 'hh:mm:ss')  as "TimeField+3hours"

5 Replies
Partner
Partner

Re: Time formating

Try:

Date( timestamp#(APP_03,6,'YYYYMMDDhhmmss'), 'DD-MM-YYYY') as DateField

Time( timestamp#(APP_03,6,'YYYYMMDDhhmmss'), 'hh:mm:ss') as TimeField

ivanaksen
New Contributor III

Re: Time formating

Thanks a lot for your answer.
Unfortunately thats not working:(

1.jpg

Partner
Partner

Re: Time formating

My bad !! I havn't delete the ,6 part related to ur left funtion !

 

Try:

Date( timestamp#(APP_03,'YYYYMMDDhhmmss'), 'DD-MM-YYYY') as DateField

Time( timestamp#(APP_03,'YYYYMMDDhhmmss'), 'hh:mm:ss') as TimeField

ivanaksen
New Contributor III

Re: Time formating

It`s perfect, thanks a lot:)

Can you advise, how can I add 3 hours in to final scope?
Thanks in advance

Partner
Partner

Re: Time formating

Maybe sthing like :

Time( timestamp#(APP_03,'YYYYMMDDhhmmss') + time#(03,'hh'), 'hh:mm:ss')  as "TimeField+3hours"