Comparing timestamp and dates

Vlad B

Hi,

I have a table where I store values for some metrics. The table structure is quite simple: metric id, userid, metric date and metric value. 

When a new metric comes in, I want to check if I already have a value for that metric id & user id for the date in question. 

I tried a query all records with 3 custom filters - 
1.  userid in table = incoming userid  
2. metricid in table = incoming metricid
3. metric date = incoming metric date (or now if no date comes in)

As I am checking at day level (I cannot have 2 values for the same metric or the same user for the same day) I tried using an epoch time filter to calculate days lapsed from start of epoch. But that did not work. I cannot find the format timestamp filters in the query all records either so I am a bit lost on how to check equality between two fields at a partial timestamp level (just the date part). 

I could probably use some loops or array filters (?), but seems an overkill. If I do a query all records, and the resulting array has a count > 0 than I dont do any updates, as I already have the value. 

Any help is appreciated. Thanks!

Michael Udinski

Hi  - are you using a date field or a timestamp field?

If you are using a date field, you should be able to set the custom query = today... where today would just be a text string.

If you are using a timestamp field. You will want to where statements:

field >= today
AND
field < tomorrow

You can see more about using relative time formats in our documentation: https://docs.xano.com/working-with-data/timestamp

Vlad B

 trying to compare a timestamp field with a date field. I have timestamp in my API response and a date in my database. And I want to check if the date in the timestamp is the same as the date in my database. I'll check your response and see if I manage to get it working for my use case. Thank you!!

Abdullah Abdulkareem

 did you end up figuring this out? Having the same question on my end as well

Michael Udinski

You will need to format the timestamp into a date to compare the two to each other. https://docs.xano.com/working-with-data/timestamp#what-are-my-options-with-formatting-date-and-time

Michael Udinski

If you're doing it inside the Query then an easy win is the timestamp_epoch_day filter

Abdullah Abdulkareem

 wow thanks for the very quick response! I'll give this a try 🙂

Riaan Backer

 - I have to two timestamp values. Start_time (8:00) and end_time (15:00) and I want to make use of a while loop in order to add 10min intervals to the start_time until it is greater or equal to the end_time. I have followed your advice by making use of the normal where comparison that comes with the while loop function but it seems that the comparison does not work.

Below is a screenshot of the formatting in place for start_time and end_time[timestamp.PNG]

Michael Udinski

 You'll need to make the comparison first before formatting the timestamp in the format you have it. 

Riaan Backer

 - thanks for the feedback. It would be very helpful to be able to compare even after you have applied formatting. I know you will have to compare apples with apples (day vs day/month vs month) in order for the comparison to work. It just gets difficult to confirm your results if you don't format it into a human readable format.

Maybe consider this as an enhancement

Riaan Backer

  - the current timestamp database data field caters for day+time. I need to do comparison on time slots only on not days. What data field type would you recommend for this in the database? The issue that I face at the moment is that because the timestamp field includes a date also I cant just compare the time. I initially thought I could but based on your advice I should not apply formatting and if I do the comparison without the format filter includes the date which the returns the wrong results.

Michael Udinski

 -

On second pass - it appears that you can 100% make comparisons of timestamps in your format.
[CleanShot 2022-03-10 at 13.36.29.mp4] Perhaps there is another piece of the logic where your error is occurring?

Riaan Backer

I have managed to solve the problem by transforming into 24h format with leading zeros(H:i). For some strange reason converting into no leading zero (G:i) it does not return accurate results.

I do a comparison by finding all elements within and array object based on a time slot.

Felix Tan-2030210

 Jumping on this thread, I'm getting this error when using the timestamp_epoch_day filter. Most filters with regards to timestamps throw the same error.[image.png]Here's the query
[image.png]

Felix Tan-2030210

SIdenote: Why isn't the format and transform filters available on search query conditionals? It would make life so much easier... 😢

Michael Udinski

 What's the schema of user.profile.work_anniversary

Also always start with the db on the left side.

Query all records operates on a different environment than variables in the function stack therefore the filters are not the same

Felix Tan-2030210

 schema is a timestamp. 

Is there anyway to get it to work with a date schema though? As that would be preferred, but I couldn't get it to work with that schema.

Michael Udinski

it looks like you just need to start with the db and it should sort itself out:[CleanShot 2022-05-06 at 10.48.35.png]For a date field, you can leverage the between filter and relative time, for example:[CleanShot 2022-05-06 at 10.50.04.png]

Michael Udinski

The Relative time php link is here in the documentation: https://docs.xano.com/working-with-data/timestamp#what-are-my-options-for-inputting-a-timestamp-into-xano-through-the-api