How to convert a utc number to real date

[#1]

Hello

I would like to convert a UTC number to a date stamp

i get this valuew from a intershop logfile numbers look like this and is the milliseconds from 01-01-1970

Is there any ony one who can help me

regards

Chrisz

these are some of the numbers

1302472796254

1302472796410

1302472796582

1302472796598

1302472797051

1302472797066

1302472797082

1302472797145

1302472797207

1302472797848

[pat_agen]

hi,

have you tried the UTC() function? see attached image

[#3]

Hi Pat

Thanks for your fast reply

This is wat I tried

LOAD @1,UTC(@1)FROM
[Data\WA-S6APPSRVPRD-1-20110411-0000.log]
(txt, codepage is 1252, no labels, delimiter is '|', msq);

which results in

so any ideas

Cheers

Chrisz

[pat_agen]

hi,

just from your posted image no.

can you load your original data without trying any modifications? does it load or do you get the error?

ie can you write this:

LOAD @1 FROM
[Data\WA-S6APPSRVPRD-1-20110411-0000.log]
(txt, codepage is 1252, no labels, delimiter is '|', msq);

?

If you can load that then try the UTC() function once you have got the date sucessfully into qlikview.

Or post a sample of your logfile

[#5]

Hi Pat

Herby the files

Vriendelijk groet

Chrisz

[pat_agen]

Hi,

i don't get your error message when laoding your test file.

Renamed the UTC(@1) field as in image and loads ok with value as timestamp. Am I missing something?

[#7]

This 1302472796254 looks like java miliseconds of Date class.

I don't think you can convert them with standart qlikview function

You can try something like this.

Calculations is wrong. I try showing logic

     MakeDate(

               ((((1302472796254/1000)/60)/60)/24)/365) +1970 ,   --Year

               ((((1302472796254/1000)/60)/60)/24/365)/12)    -- Month

               ((((1302472796254/1000)/60)/60)/365)   --    Date

)

I face this problem but this is was on database level (ms sql)

and i use dateadd function.

[#8]

hi vladimir

when i try your example i get no result

[#9]

Load MakeDate(1970, 1, 1 ) + (YourDateColumn / (1000 * 60 * 60 *24)) as ResultingTimestamp

Basically you want to add the base date with the number of days since 1-1-1970. To do this you divide your timestamp per number of milliseconds in a day, then sum your base date.

This should work even for leap year adjustments.

If the result is not displayed correctly, check the tab Number in the Document Properties.

Good luck!

Edit: fixed the # in MakeDate (copy and paste pasted an invisible hyperlink...)

[Miguel_Angel_Baeyens · Accepted solution]

In addition to this provided bye chiesa80, which seems to me the closest approach to what you want to get, the following will return the complete timestamp in an interpretable format by QlikView:

LOAD *,
     TimeStamp(MakeDate(1970) + (OriginalTimeStampField / (1000 * 60 * 60 *24)), 'MM/DD/YYYY hh:mm:ss.fff') AS TimeStamp
FROM Source

Now you can extract month, date, day, week and so from the field TimeStamp. Note that MakeDate doesn't have the "#" sign (probably a typo).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica