Working with Timestamps in Qlik Sense: 7 Practical... - Qlik Community

Ouadie · ‎2025-05-23

Handling simple calendar dates in Qlik is "usually straightforward". Timestamps, on the other hand are a different story. Fractions of a day, time-zone offsets, and “why won’t this sort?” moments can slow you down when developing an app.

In this blog post, I compiled 7 tips that can help you when dealing with Timestamps in Qlik.

1- Know Your Duals

Every Qlik timestamp has two sides:

Numeric side: days since 1899-12-30 (midnight = whole number)
Text side: the display mask Qlik generates

Num(MyTS)   // 45435.6542   (barcode)
Text(MyTS)  // 2024-05-22 15:42:05   (tag)

	Purpose	Example
Formatters Timestamp() / Date() / Time()	Change only the display text (tag)	Date(MyTS, 'MMM-YYYY') → “May-2024”
Interpreters Timestamp#() / Date#() / Time#()	Take raw text, build a new dual value, i.e. create the barcode.	Timestamp#('2024-05-22 15:42', 'YYYY-MM-DD hh:mm') → dual value: - numeric: 45435.65417 - text: “2024-05-22 15:42”

Usually, issues with filters not working etc... will come from loading a timestamp as plain text and trying to format it later, changing only the "tag", never creating the "barcode".

2- Parse Once, Keep the Raw Text

Raw timestamps often land in your Qlik Sense App in different ways:

2025-05-23 14:31
5/23/25 2:31 PM
Unix epochs
Excel serials
etc...

Instead of trying to print them in a pretty way everywhere, you can instead:

Interpret the text a single time in the load script.
Store the parsed dual in a clean field (TS).
Keep the raw field off to the side for future re-parse.

LOAD
    RawTS,                                         // original column
    Timestamp#(RawTS,'YYYY-MM-DD hh:mm:ss') AS TS  // dual value
...

You can use "TS" for date calculations, for instance:
- Floor(TS) AS OrderDate
- Frac(TS) AS TimeKey

and keep "RawTS" hidden for QA, or re-parsing later.

3- Floor() + Frac() — Split Calendar and Clock

Timestamps captured to the second (or millisecond) contain far more detail than most analyses need.
Using that raw granularity in filter panes or chart axes can crowd the UI with millions of distinct values and add unnecessary processing overhead, so a better approach would be to split each timestamp into a date part and a time part so you can control it better

	Function	Example result	Why?
OrderDate	Floor(TS)	2024-05-22	Links neatly to your master calendar—great for YTD, MoM, etc...
TimeKey	Frac(TS)	0.60486… (14:31)	Lets you build a small “clock” dimension for charts

LOAD
    Floor(TS)                       AS OrderDate,   // 2024-05-22
    Round( Frac(TS), 1/1440 )       AS TimeKey_1min // 00:00 … 23:59
...

Why round?

Minute precision (1/1440 of a day) keeps the time dimension at 1440 values, this is perfect for charts, while OrderDate links neatly to your master calendar.
Clearer visuals: users choose “14:30” instead of scrolling through every second.
Predictable model size: 1440 rows in a minute-level time table, regardless of how much fact data you load.
Flexible math: the original TS is still there if you need second-level calculations.

For 15-min blocks, you'd do this:

Round( Frac(TS), 1/96 ) AS TimeKey_15min

4- Master Time Table

After you’ve split the timestamp into Date and TimeKey, you still need a small lookup table so charts can show friendly labels like “14 : 30.” That’s where a Master Time table comes in. Below, we generate exactly one row per minute, no more no less, so that our app gets a clean predictable clock dimension.

MasterTime:
LOAD
    Round(IterNo()/1440,1/1440)               AS TimeKey,  // numeric 0–0.9993
    Time(Round(IterNo()/1440,1/1440),'hh:mm') AS TimeLabel  // 00:00 … 23:59
AUTOGENERATE 1440;   // 24 h × 60 m

	Explanation
IterNo()	Produces 0 … 1439 during AUTOGENERATE.
/ 1440	Turns each integer into the fraction of a day (1 = 24 h).
Round(…, 1/1440)	Rounds the fraction exactly on the minute boundary.
Time(…, 'hh:mm')	Formats the fraction as “00:00”, “00:01”, … “23:59”.

Need 15-minute buckets? Change 1440 to 96 and 1/1440 to 1/96.

Check out Hic's blog post here for more in-depth information on this topic.

5- Converting Odd Formats (Unix Epoch & Excel Serial)

Not every data source delivers nice ISO date strings. Two formats you'll encounter most of the time:

	What it means	Typical sources
Unix Epoch	An integer that counts seconds since 1970-01-01 00:00 UTC. Example 1716460800 is 2024-05-23 00:00 UTC	REST APIs, server logs, Kafka streams etc...
Excel Serial	A decimal that counts days since “1899-12-30 00:00” (Excel's day 0). Example 45435.75 is 2024-05-22 18:00	CSVs saved from Excel etc...

Load once, store as a proper dual, format later however you like.

// Unix Epoch (seconds) to Qlik timestamp
(Epoch / 86400) + 25569          AS TS_UTC     // 86400 = sec per day

// Excel Serial to Qlik timestamp
ExcelSerial + Date#('1899-12-30') AS TS_Excel

Why 25569?
-> That's the number of days between Qlik (& Excel)'s day 0 (1899-12-30) and Unix's day 0 (1970-01-01).

If you need a static timezone conversion? Add or subtract the offset in days:

(Epoch/86400)+25569 + (-4/24)    AS TS_Local   // convert to UTC-4

6- Turning Raw Seconds into Readable Durations with Interval()

When you subtract two timestamps Qlik gives you a fraction of a day and gives you something like: 0.0027. But this doesn't really tell you the actual duration. You probably would understand something like this instead: 00:03:55.

That’s what the Interval() function is for, to convert numeric time spans into clean, readable timestamps.

// Average call duration  →  03:55
Interval( Avg(EndTS - StartTS) , 'hh:mm' )

If you need the raw number too, you can pair Interval() with Num():

// friendly + raw seconds

Interval(Avg(EndTS - StartTS),'hh:mm')   AS AvgDuration,
Num( Avg(EndTS - StartTS) * 86400 )      AS SecDuration

The result: The dashboard objects show 03:55 while any export still carries the raw value 222 seconds for downstream math.

Interval() is the quickest way to turn fractions of a day into durations everyone understands.

7- Applying a Simple Time-Zone Offset

Qlik stores every timestamp as a serial day-count with no built-in timezone. If your source data arrives in UTC but end users need to view time in Eastern Time, you can apply a static offset right in the load script:

// 1 hour = 1/24 of a day
LET vOffset = -4 / 24;        // EST (UTC - 4 hours)


...
LOAD

    UTC_TS,
    UTC_TS + $(vOffset) AS LocalTS
...

Result:
LocalTS shows 08:00 when UTC_TS is 12:00.

When a static shift is enough

Historical data analyses (offset never changes).
Systems that log in a single, known zone.

Handling daylight-saving shifts

If you need results for different regions that switch between standard and daylight time:

	How
ConvertToLocalTime()	ConvertToLocalTime(UTC_TS, 'US/Eastern', 1) More info on the help docs	- Built-in Qlik function - Pass “1” to auto-adjust for DST.
Handle at the source	Do the conversion at the source DB, ETL tool, or API	Keeps Qlik lean and avoids re-computing on every app reload.

• For a fixed zone, add or subtract hours / 24.
• For regions with daylight changes, use ConvertToLocalTime() or adjust before the data hits Qlik.

That's all for this post. Time is certainly tricky, but once you understand Qlik’s dual-value modeland leverage the tips and tricks above, it becomes just another dimension in your analytics!

Thanks for reading!

Working with Timestamps in Qlik Sense: 7 Practical Tips