topic Re: How to convert a utc number to real date in QlikView

How to convert a utc number to real date

Mon, 06 Jun 2011 07:55:14 GMT

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

How to convert a utc number to real date

pat_agen — Mon, 06 Jun 2011 08:06:03 GMT

hi,

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

How to convert a utc number to real date

Mon, 06 Jun 2011 08:14:26 GMT

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

How to convert a utc number to real date

pat_agen — Mon, 06 Jun 2011 08:25:22 GMT

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

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 08:48:26 GMT

Hi Pat

Herby the files

Vriendelijk groet

Chrisz

How to convert a utc number to real date

pat_agen — Mon, 06 Jun 2011 08:56:43 GMT

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?

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 09:17:51 GMT

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.

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 10:13:05 GMT

hi vladimir

when i try your example i get no result

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 10:35:48 GMT

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

Re: How to convert a utc number to real date

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 10:53:39 GMT

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

Re: How to convert a utc number to real date

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 11:01:29 GMT

Hi,

UTC() always returns real time (and gets updated every second) in UTC format, regardless the parameters you pass on it, so UTC(Now(2)), UTC(ABC) and UTC(2) and UTC() will return the same (at least in version 10 SR2). So it will always return the same date (now) regardless the value it gets from the field.

It would be great though if parameters will not be allowed or it throw an error when something is set within the brackets.

Hope that helps

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 11:06:17 GMT

Thanks Miguel, fixed the typo in original post.

Re: How to convert a utc number to real date

Mon, 06 Jun 2011 11:09:03 GMT

THank you all guys and girls

for you support

cheers

Chrisz