Sorting by Datetime doesn't work after a certain time for Wufoo API in Python

Question

I am using the Wufoo API to pull data from people entering data into a form on a website. I am using date time in the format “YYYY-MM-DD HH:MM:SS” For example: 2009-08-13 11:43:22.

When I use the last date data was entered (2017-11-10) and put it to show any time after 11:00:00, it doesn't show any results, even though there are a few after 13:00:00. If I use any time before 11:00:00 it displays all results from that day. I noticed if I just put "11:00:00" it shows results after 14:00:00, but not the ones after 13:00:00. This is the filter part I'm using:

Filter1=DateCreated+Is_after+2017-11-10%2011:43:22'

The "%20" is how to encode a space into a URL. Any ideas why it doesn't show results accurately? Is it doing a calculation with the %20 instead of using it as a space?

Here is a link to the section outlining the Wufoo API datetime: https://wufoo.github.io/docs/#form-entries

Scroll down a little bit where the valid operators are shown and the MySQL datetime format is mentioned.

Answer 1

According to your linked doc, DateCreated is the date/time recorded in the timezone of the user making the request.

According to https://dev.mysql.com/doc/refman/5.7/en/datetime.html MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

So most probably it does not work because somehow it creates entity and store time with your wufoo server's timezone and later operator 'is_after' compares provided datetime as it would with db's timezone.

I guess that database connections are out of your control so the best place to ask for that issue is wufoo blog: https://www.wufoo.com/blog/

NOTE: that's first time when I've heard about wufoo ;)

Answer 2

Your assumption is incorrect.

The proper way to encode a space in a URL is with a +.

Thus, the following should work

Filter1=DateCreated+Is_after+2017-11-10+11:43:22