Error on Date Time Filter?

dev2
Participant V

Hi all,

I’m using a slice for sending a daily SMS, but the slice does not seem to get the expected rows.

I’m trying to get rows that have a “Date Chgt Statut’” within the last 24h (this is my column reporting TimeStamp from another Table).

I tried two formulas (which I think equivalent) in this purpose, but the result is always the same : I’m getting 3 rows instead of 7 expected.

This slice uses a filter formula as following :
[Date Chgt Statut]>datetime(NOW()-1)
Here is the second formula I tried:
AND(NOW() > [Date Chgt Statut], HOUR(NOW() - [Date Chgt Statut]) < 24)

The column field where originally “date” formatted, and my formula was instead :
[Date Chgt Statut]>DATE(NOW()-1) (and not DATETIME)
…the result was still 3 rows.

Here is what I have on the data slice view:
2X_7_788fbaeaa01547fd1a32095c64663895fcbb9f43.png
And what I expect :
2X_b_b885d041300d3f155a4615499f0edef6aadeff78.png

Do you have any clue of what I’m missing ?
Is it because the missing items are beyond the thousandth row ? I didn’t read about any limiting rows in the appsheet center.

Many thanks for your help,

Aurelien

Solved Solved
0 8 1,085
1 ACCEPTED SOLUTION

@dev
Your [Date Chgt Statut] column is a DataTime type and your evaluation produces a Date type. Actually; lets get an example data from Row#472 for this evaluation:


[Date Chgt Statut]>date(NOW()-1)


[Date Chgt Statut] = 1/20/2020 6:08:12
NOW() = 1/21/2020 19:09:23
NOW() - 1 = 1/20/2020 19:09:23
DATE(NOW() - 1) = 1/20/2020 12:00:00
which will evaluate to FALSE
In fact your expression shall evaluate to TRUE if and only if time portion of your DateTime column is greater than 12:00:00 AM.

View solution in original post

8 REPLIES 8

LeventK
Participant V

@dev
Depending on what time-frame you wish to consider with the last 24hr, you can build the expression like:

//For filtering 1 day previous records from 00:00:01 - 23:59:59
AND(
	[Date Chgt Statut] >= DATETIME(TODAY()-1&" "&TIME("00:00:01")),
	[Date Chgt Statut] <= DATETIME(TODAY()-1&" "&TIME("23:59:59"))
)

OR

//For filtering 1 day previous records compared to NOW()
AND(
	[Date Chgt Statut] >= DATETIME(TODAY()-1&" "&TIMENOW()),
	[Date Chgt Statut] <= NOW()
)

Awesome !
Thank you, I will use your second tip, which match my expectations.
Do you know anything about a record limit for viewing data from a slice ?

Actually, I just tried the second formula, and with my new set of data I was supposed to get 6 items :

  • my previous formula gave 6 items
  • the second formula gave 3 items

Something must be wrong, somewhere.
Here is the formula that seems to work :
[Date Chgt Statut]>date(NOW()-1)

Maybe I should look in another direction

@dev
Your [Date Chgt Statut] column is a DataTime type and your evaluation produces a Date type. Actually; lets get an example data from Row#472 for this evaluation:


[Date Chgt Statut]>date(NOW()-1)


[Date Chgt Statut] = 1/20/2020 6:08:12
NOW() = 1/21/2020 19:09:23
NOW() - 1 = 1/20/2020 19:09:23
DATE(NOW() - 1) = 1/20/2020 12:00:00
which will evaluate to FALSE
In fact your expression shall evaluate to TRUE if and only if time portion of your DateTime column is greater than 12:00:00 AM.

@LeventK

Thank your your patience and explanations.

At trying again, I got a different result that I could not explain, looking like I was missing something else…Fortunately, you are more experienced than I am on it. Thank you very much !
I didn’t realize that the “date” type make calculation ending on a “12:00:00”. I will keep that in mind for a further development !

Thanks again !

@dev
AppSheet platform is mainly based on AJAX. Either in AJAX or in JAVA in general, there is no Date and no Time properties alone, there is only DateTime. However, Date and Time are needed properties in development so, in essence both are evaluated as DateTime which is called Joda Time [1]

Epoch: The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Basically, to make the date and time calculations correctly, the system needs to convert them to milliseconds and to do this, the date and time values shall be converted to a datetime value. [2],[3]


Date Value
1/21/2020
is converted to
1/21/2020 12:00:00 AM

Time Value
9:05:43 PM
is converted to
12/30/1899 9:05:43 PM


As you can easily notice, both date and time values are in fact evaluated as timestamps (datetime)

[References]


[1] https://www.joda.org/joda-time/
[2] https://en.wikipedia.org/wiki/Unix_time
[3] https://en.wikipedia.org/wiki/Epoch

Thank you very much for this piece of knowledge !

It works to me! Gracias!!

Top Labels in this Space