Tidy up date/time field

[Dayna]

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

[swuehl · Accepted solution]

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

or

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

[Dayna]

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

[swuehl]

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).

[Dayna]

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

Thank you for your help!

Kind Regards,

Dayna

[swuehl]

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