Weird behavior on timestamp

Leo Med

Let me explain. I'm on GMT +1 like for all my current customers.
I need somewhere to get daily records, so I go by comparing epoch days

Where date is sent from the frontend as a return Math.floor(timestamp / 1000 / 60 / 60 / 24).
However, for records in this 1 hour timezone offset, 00:35:48 GMT+1 for example, the frontend epoch day is xxx but the backend one is still xxx-1 , so I'm unable to get the right data for the customer and that could create misunderstandings while operating at that offset time.
Fortunately, I have my customers timezones(in hours) in the settings table, so with a function to get that value, I came to this.

But I can't even explain the result it produced, nothing good(for what I want to do). I replaced the timezone variable by 0 thinking it would cancel the add_hours effect but no. It is like the filter is doing something I'm not getting. I ended up removing the filter and timezone.

Am I doing something wrong? Thank you for the help

arturosanz

@Leo Med keep in mind that in Xano, timestamps and integers are different types, so don't expect to get good results comparing them. One of the reasons is because the value 0 as timestamp is a valid date Thu Jan 01 1970 00:00:00 GMT+0000 but 0 as integer is 0. The date variable is an integer, not a timestamp, and very likely that's the problem you have.

Michael Udinski

Hi @Leo Med - working across time zones is tricky. It sounds like your issue here is that you're using the timestamp_epoch_day filter. Epoch day is going to be the count of days since Jan 1, 1970, 00:00:000 UTC. All of the epoch filters will be anchored to UTC.

The timestamp filters without epochs in them allow you to define a timezone.

Are you trying to say timestamp_add_hours and define a timezone? This filter needs an integer value.

I'm wondering if you need that formula at all from your front-end, depending on what the value is your passing from your front-end (pre-front-end logic), Xano could make it easier to filter this data.

Leo Med

Thank for your response @Michael Udinski

Are we first okay that the logic above should work the same as the one below ?

If it should not, then why? (It actually doesn't)

How does Xano calculate the timestamp epoch day, if I can ask?

Michael Udinski

Epoch day is going to be the count of days since Jan 1, 1970, 00:00:000 UTC. 

Is there any reason you don't just input a date? I think it would make things easier; you could do something like:

Michael Udinski

Where UTC is replaced by the user's timezone

arturosanz

@Leo Med notice in Michael's example that the date input is of type date not integer.

Leo Med

Thanks @Michael Udinski, timestamp_day_of_year filter will do it.

Leo Med

@arturosanz , thanks for the reply, but I never compared timestamp to integer. I just compared integer to integer. The timestamp_epoch_day filter returns an integer value😅

Leo Med

Last request @Michael Udinski please, for peace of mind. This should return true or false ?

Leo Med

arturosanz

@Leo Med the label date as integer input confused me. Maybe days would be more appropriate.

I've done some testing on timestamp_add filters and found strange results too. Your WHERE statement should be true, of course, but unfortunately it is false.

The screenshots below use the timestamp_add_seconds because I wanted to use the highest precision possible.

Basically there is an interval in which the == returns true but it's not evident at all.

All numbers between -122399.999999 and -36000.000001 seconds (-34.999999 and -10.000001 hours) make the WHERE clause true. This is aprox a 24 hours interval, which is fine for the timestamp_epoch_day filter, but shifted 10 hours earlier! Strange, isn't it?

This returns true…

This also returns true…

And any number in between also returns true. Everything else returns false. That's why 0, which is greater than -36000.000001, returns false.

@Liz Anaya please, check out this post.

Leo Med

Thank you so much for investigating this one @arturosanz 🙏 , thought I was going creazy😄. Hope they will take a look on that asap. Sincere thanks to you🙏, have a great day.