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