Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split date and time from a time field.

Hi, I have found the need to split date and time from a timestamp field.

This is how the field looks like in the source:

03.03.png

I have managed to split out the date by the use of date() - function

date(floor(create_time),'DD.MM.YYYY') as Dato1

The tricky part looks to be to just get out the whole hours from the create_time field.

What I have tried so far without any success.

frac(round(create_time,(1/24))) as [Incoming Time],

time(frac(create_time),'hh')   as [Incomming Time], Time#(Right(create_time,8),'hh') as [Incoming Time],

Time(Frac(TimeStamp#(create_time,'DD.MM.YYYY hh:mm:ss')),'hh') as [Incoming Time],

date(floor(create_time),'hh') as [Incoming Time],

Does anyone have any idea on how to achieve a "timestamp" with just the whole hour?

In my case, i just want 24 unique values from 00 - 23. From all the 21 million rows in the create_time field.

By using frac(round(create_time,(1/24))) as [Incoming Time],

I got this result, but the end result should be without Date and mm:ss just the hh left.


close.png


- Stian K.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You were almost there, I think with

frac(round(create_time,(1/24)))

Now just rearrange and format that - I think like this:

Time(Round(Frac(create_time), 1/24), 'hh')

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
MK_QSL
MVP
MVP

may be something like below .... not 100% sure

=Time(create_time,'hh:00:00')

sunilkumarqv
Specialist II
Specialist II

You can use Dual function to split the date and time

=Dual( Date( Floor( Date ), 'YYYY-MM-DD')  & '  _time ' Time( Frac( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss') as Incoming Time

Hope it helps

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You were almost there, I think with

frac(round(create_time,(1/24)))

Now just rearrange and format that - I think like this:

Time(Round(Frac(create_time), 1/24), 'hh')

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Or even this:


Round(Frac(create_time) * 24), 1)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Yes, this also worked. Just added the missing (

Round((Frac(create_time) * 24), 1) as [Incoming Time],

Not applicable
Author

Thanks worked like a charm