Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to convert a utc number to real date

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

1 Solution

Accepted Solutions

Re: How to convert a utc number to real date

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

12 Replies
pat_agen
Valued Contributor

How to convert a utc number to real date

hi,

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

utc.png

Not applicable

How to convert a utc number to real date

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

2011-06-06_1012.png

so any ideas

Cheers

Chrisz

pat_agen
Valued Contributor

How to convert a utc number to real date

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

Not applicable

Re: How to convert a utc number to real date

Hi Pat

Herby the files

Vriendelijk groet

Chrisz

pat_agen
Valued Contributor

How to convert a utc number to real date

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?

UTC2.png

Not applicable

Re: How to convert a utc number to real date

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.

Not applicable

Re: How to convert a utc number to real date

hi vladimir

when i try your example i get no result

Not applicable

Re: How to convert a utc number to real date

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

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