Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

insideventures1
New Contributor

Create a field for Hour of Day from Timestamp

I have a timestamp field in my data and I need to create a new field that only shows a value for the hour of day in a 24 hour format.  E.g. original timestamp shows 07/24/2016 2:15:32PM and I need to create a dimension field called "Hour of Day" that would only show the value "14" (2PM in 24hr format).

Original timestamp field (named date_created) is coming in this format: YYYY-MM-DD hh:mm:ss

Thanks!

2 Replies
oknotsen
Honored Contributor III

Re: Create a field for Hour of Day from Timestamp

I suggest using the hour() function .

hour - script and chart function ‒ Qlik Sense

May you live in interesting times!
MVP
MVP

Re: Create a field for Hour of Day from Timestamp

okg‌'s solution should work like a charm for you, except if your timestamp is read as text by Qlik Sense. If it isn't read as a timestamp field, Qlik Sense won't be able to apply Hour() function before you manually help Qlik Sense interpret your timestamp as a date and time field. To do this, you can use Qlik's interpretation function (Date#(), Time#(), TimeStamp#(). In you case if simple Hour(date_created) doesn't work, then try this:

Hour(TimeStamp#(date_created, 'YYYY-MM-DD hh:mm:ss')) as Hour_Created

But you need to make sure that your timestamp format is what you say it is. The reason i say this is because your example mentioned a time format of MM/DD/YYYY h:mm:ssTT and you claim that it is YYYY-MM-DD hh:mm:ss. Make sure to use the right format to read the date and time correctly. Read more about dates in Qlik here:

Get the Dates Right

Why don’t my dates work?

Best,

Sunny