Efficient date and time handling
In many systems, time is either just a piece of text or, in the better case, something that you can enter via a calendar popup dialog. The system itself then barely understands the meaning.
On the other hand, we people understand time pretty well. We know that a certain date is associated with a day of week. We know that we typically do not work during the weekend. We know that Christmas are in the fourth quarter.
We would like our systems to naturally understand this as well. And guess what, Lumeer does exactly that.
The easiest way to proceed is to tell Lumeer what format of date and time we have or expect. Lumeer tries to guess that for you, however, it might be difficult to recognize month and day when the numbers do not exceed 12 for instance.
Let’s have a look at our example with data about calls made by our sales department. In the table with calls statistics, we right-click on the Call Time column header, select Attribute type and switch it to Date.
Next, we select a Custom format and enter a sort of a magic formula (see below) that reflects our format. We can see in the example that the format matches.
In the following table, we can see what is the meaning of individual codes in the date time format formula:
|YYYY, YY||Years 2021, 21|
|MM, M||Months 01-12, 1-12|
|DD, D||Days 00-31, 0-31|
|HH, H||Hours 00-23, 0-23|
|hh, h||Hours 00-12, 0-12|
|mm, m||Minutes 00-59, 0-59|
|ss, s||Seconds 00-59, 0-59|
|S, SS, SSS||Milliseconds 0-999|
|a, A||am/pm, AM/PM|
|DDD, DDDD||Day of year 1-365|
|ddd, dddd||Day of week Mon, Monday|
|e, E||Day of week 0-6, 1-7|
|WW, W||Week of year 01-53, 1-53|
|x, X||Timestamp 1410715640579, 1410715640.579|
|Z, ZZ||Offset from UTC +12:00|
The advantage of Lumeer understanding the format is that we can create a Pivot table that counts total and unique calls per day of week and hour of the day.