topic Re: Tidy up date/time field in QlikView

Tidy up date/time field

Dayna — Fri, 12 Oct 2012 10:56:39 GMT

Hello All,

Could you advise on the best way to format the below so it shows as DD/MM/YYYY hh:mm:ss - and clear the duplicates?

2012-09-28 17:46:10:649 + 01:00

2012-09-28 17:46:10:659 + 01:00

2012-09-28 17:46:10:662 + 01:00

2012-09-28 17:46:10:670 + 01:00

2012-09-28 17:46:10:672 + 01:00

2012-09-28 17:46:10:676 + 01:00

2012-09-28 17:46:10:678 + 01:00

Kind Regards,

Dayna

Re: Tidy up date/time field

swuehl — Fri, 12 Oct 2012 11:07:05 GMT

You should be able to do it like

timestamp#(timestamp(Timestamp#(left('2012-09-28 17:46:10:649 + 01:00',19),'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as NEWFIELD

timestamp#(timestamp(Timestamp#(left(YOURFIELD,19),'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as NEWFIELD

or if your standard timestamp format matches the required format, just:

timestamp#(timestamp(Timestamp#(left(YOURFIELD,19),'YYYY-MM-DD hh:mm:ss'))) as NEWFIELD

Re: Tidy up date/time field

Dayna — Fri, 12 Oct 2012 11:11:04 GMT

Hello Swuehl,

Many thanks for your response! Why do you need the additional two timestamp's? Would Timestamp#(left(YOURFIELD,19),'YYYY-MM-DD hh:mm:ss') do the job? If you could explain the first two timestamps and why you did it that way, it would be appreciated - as I do struggle with date/time formatting!

Kind Regards,

Dayna

Re: Tidy up date/time field

swuehl — Fri, 12 Oct 2012 11:18:06 GMT

Timestamp#(left(YOURFIELD,19),'YYYY-MM-DD hh:mm:ss')

should do the job to interpet the input value as timestamp (Hm, one thing I was too lazy about in my first answer: do you need to handle different timezones (the + 01:00 part) in your input string?).

The internal use numeric value will still be different if the millisecond part is different (values you called duplicates, I think). The other two timestamp functions are used to get rid of the duplicates (first change the text format to the desired format, then interpret this text (without the milliseconds) as new numeric value).

Re: Tidy up date/time field

Dayna — Fri, 12 Oct 2012 12:27:56 GMT

I might need to take into consideration the +01:00 - would that be possible?

Thank you for your help!

Kind Regards,

Dayna

Re: Tidy up date/time field

swuehl — Fri, 12 Oct 2012 16:37:54 GMT

Should be possible.

There is a QV function to convert a timestamp to local time:

ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])

There are some few threads that talk about time zone handling, e.g.

http://community.qlik.com/thread/40060

If you have problems, I could help you maybe next week.

Regards,

Stefan