Qlik Community

QlikView Documents

Documents for QlikView related information.

Logic behind date conversion

utkarshgarg
Contributor II

Logic behind date conversion

We use the following expression and it throws us a number.

Num(Now()) = 42097.8033333 (A snapshot of current timestamp)

Here the timestamp is : 03-04-15 7:16:47 PM (According to qlikview) or 03-04-15 19:16:47 (DD/MM/YY hh:mm:ss)

Two questions had been bugging me for some time:

  1. How does qlikview convert a timestamp into a number? (logic?)
  2. How does qlikview convert a number into a timestamp? (again, logic?)

Finally, I was able to decode the logic behind this conversion. Here we go:

Answer to Question 1

Timestamp = 03-04-15 19:16:47

Let’s take the date part first : 03-04-15

Qlikview starts its numeric calendar from 31-12-1899. i.e. 31-12-1899 is equivalent to numeric 1. in excel 01-01-1900 is equivalent to numeric 1.

After that it just keeps adding days to the 31-12-1899. For example: 03-04-2015 will be 42097, i.e. it has been 42097 days since 31-12-1899.

Now coming to the interesting part, TIME!

Time = 19:16:47

What qlikview does is, it brings the time part to the day level. i.e. Converting it into number of days.

Lets take the hour part first: 19 hours

Now in 1 day we have 24 hours so in 19 hours we have = 19/24 = .79166 Days (.79166666666666666666.. to be exact)

Similarly 16 minutes will have 16/(24 * 60) days = .01111 Days                       (.0111111111111111.. to be exact)

17 seconds will have 17/ (24 * 60 *60) days = .00019 Days                              (.000196759259.. to be exact)

So, the total days elapsed the time part represent is .79166+.01111+.00019 = .80296 ~ .80333 days  (due to above approximation)

So, the timestamp in a unified form is 42097.80296 days ~ 42097.80333 days.

Same logic could have be applied if we wanted to show the number format in hours or minutes or even seconds instead of days. It is similar to showing distances in Miles,Kms and Meters.

Answer to Question 2

Timestamp in number format = 42097.80333

Same logic for the part before decimal.

So we have Date = 03-04-15

Now, the part after decimal point.

If I want to generalize the expression I would write :

HH / 24 + MM / (24*60) + SS / (24*60*60) = T

Here T is a number less than 1. (T = .80333)

We notice that, all the three components before equal to sign in the above expression are less than 1.

If we multiply the expression by 24 we get :

HH + MM / 60 + SS /(60 *60) = T *24

Now we notice that, HH>1 though others are still less than 1. So what we get after multiplying our decimal part is something like X.abc = T *24 or (19.27992 = .80444 * 24) since only the hour part is greater than 1 we can say that X is the Hour part in the timestamp.

For calculating the Hour: 0.80333* 24 = 19.27992 (bringing the number to the hourly level)

Take the part before decimal and you will get 19.

Minutes : 0.27992 * 60 = 16.7952 (Taking the decimal part from the previous number of hours and multiplying it by 60) because MM + SS/60 = T*24*60 – HH *60 = 16.7952 and since, MM >1 , that implies MM = 16

Therefore, minutes = 16

Seconds : .7952 * 60 = 47.712

Therefore, seconds = 47

You can go forward and calculate milliseconds, microseconds or whatever you want to.

Magical indeed!

No it isn’t. It’s simple maths!

Hope you found this article interesting.

Thanks,

UG

Tags (3)
Comments
jacknick
New Contributor II

Good stuff here but the QlikView Date Technical Brief (which can be found at...

https://community.qlik.com/servlet/JiveServlet/downloadBody/2811-102-5-9742/DateFields-TechBrief-US_... ) states that "The serial numbers used for dates are the same as

in Excel: the number of days passed since the 30th

December 1899 using the Gregorian calendar."

Might be worth double checking?

0 Likes
utkarshgarg
Contributor II

Actually I checked. 1st January,1900 gives me 1 in excel when converted to number.

Capture.JPG

0 Likes
carbal1952
Contributor II

This is a monstrosity !

0 Likes
utkarshgarg
Contributor II

Don't know if that is a positive or a negative comment

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-04-05 06:28 AM
Updated by: