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: 
jananireddy1
Contributor III
Contributor III

Search condition for date in a timestamp field

Hi Fellow Qlikers, 

I am trying to search a column of a table. The field coming from db is a timestamp field. 

jananireddy1_0-1615577188481.png

 Is there a way i can ignore the timestamp and just be able to write my search condition as  >=02/20/2020 and it still fetches me results 

jananireddy1_1-1615577274969.png

 

 

JR
1 Solution

Accepted Solutions
eddie_wagt
Partner - Creator III
Partner - Creator III

Hello @jananireddy1 ,

Could you check if this expression is better? I cannot test it myself.

=dual(created_date,num(Timestamp(created_date)))

Regards

Eddie

View solution in original post

13 Replies
Or
MVP
MVP

I attempted to replicate this on my end, but I'm getting the correct values returned when I search like you described (albeit with a different timestamp format):

Or_1-1615580249311.png

Or_0-1615580211645.png

It looks like your values are correct timestamps, so I wonder if this might be related to the formatting somehow...

jananireddy1
Contributor III
Contributor III
Author

Hi, Any pointers as to how to correct the formatting

In the data load editor I have the standard SET dateformat and timestampformat commands

JR
Or
MVP
MVP

Nothing specific - if you could provide sample data, perhaps the issue will be visible, but working blindly I have no recommendation except to explicitly format the field in question as e.g. Timestamp(Field) or Timestamp#(Field) as appropriate.

Also, if you don't need the timestamp aspect at all, it'd probably be better to just filter on a date field, e.g. dayname(Field).

jananireddy1
Contributor III
Contributor III
Author

Sure let me work on getting you a sample app.

JR
jananireddy1
Contributor III
Contributor III
Author

Hi, I  have attached a sample app to replicate my issue

Thankyou

JR
Or
MVP
MVP

Well, sorry I'm no help, but I'm officially confounded here.

Load Timestamp(Now(),'DD/MM/YYYY hh:mm:ss') as TestTime
Autogenerate 1;

Load Timestamp#(TimeStamp,'DD/MM/YYYY hh:mm:ss') as TimeStamp INLINE [
TimeStamp
03/03/2020 11:11:11
04/04/2020 5:55:55
01/01/2020 3:33:33 ];

Both fields get picked up as $numeric and $timestamp, but TestTime won't let me search e.g. >01/01/2020 whereas TimeStamp will let me search e.g. >01/01/2020.

Either I'm missing something (likely) or this is some sort of bug / unintended behavior / something of that nature (less likely), but I'm fresh out of ideas as to what I might be missing.

jananireddy1
Contributor III
Contributor III
Author

No Problem, i appreciate your help so far on this. Let me try to do some more research on my end .. may be it has to do with the format. 

Thankyou!

JR
eddie_wagt
Partner - Creator III
Partner - Creator III

Maybe if you make it a dual. 

Like this 

=dual(Timestamp(created_date),date(floor(created_date)))

Regards Eddie

jananireddy1
Contributor III
Contributor III
Author

HI @eddie_wagt with the dual function it definitely helped be search. So that's what i wanted, But this formula is eating up a part of my data. 

If you look at the image the dates highlighted in yellow does not show when we use dual function. What i feel its doing is showing just one timestamp entry for March 2nd 

jananireddy1_0-1617733497856.png

Thankyou

JR