I believe that the key to the mismatch that you see is that you use two different database engines: one in your development environment and another in your production environment, and they have very different date and time handling.
SQLite does not have a specific date / time data type . Dates and times can be stored in text format, floating point quantities or integers, and various functions are used to convert the input data into one of these forms for storage or comparison purposes. In addition, it does not have any particular datatype such as date-time with time zone or even full support for converting time zones between arbitrary time zones.
Postgresql has comprehensive support for dates, times, intervals, and timestamps with and without clocks. It also has operators specifically defined to compare them with each other.
I suspect Django is using a text form when using SQLite as a database. If so, then a WHERE clause similar to this is given:
WHERE (to_date >= '2015-08-22 14:01:56.663072')
it will use a simple text comparison, which seems to work fine, since you can compare two ISO8601 lines this way (as long as you don't care about time zones).
In a production environment, you use postgresql with a time type with a time zone. This means that the data stored in the database is stored with the time zone information attached.
There are several time zones in this scenario:
- The time zone of your Django / python environment
- The system time zone on the computer running the client software (Django)
- The time zone of your database (configured using the timezone configuration parameter, which you can check with the
SHOW timezone; command SHOW timezone; on psql prompt). - Any time zone information is embedded in strings sent to the database.
According to your comments, you have checked at least some of these settings.
One possibility is that when records were inserted into the database, the to_date field included the time zone, and this was not the same as the postgresql database. For example, consider the following:
ispdb_t2=> select '2015-08-22 00:00:00-10:00'::timestamp with time zone >= '2015-08-22 14:01:56.663072'; ?column? ---------- t (1 row)
It seems that he believes that 2015-08-22 00:00:00 is> = 2015-08-22 14:01:56.663072 . What actually happens here is that the timestamp on the left has a time zone of -10, and the timestamp on the right is not added to the timezone, so it is interpreted in the time zone of the database session (which in my case is Australia / Victoria ', currently UTC + 10 hours).
To check if this is a factor in your case, I would suggest the following steps:
Check the value of the "postzone" configuration parameter in the "Time Zone" parameter. Note that it is configured per session - so you must also confirm if Django sends the SET timezone command after connecting
Verify that SQL is used to insert values into the database. Confirm if the time values correspond to or not the built-in time zones.
Confirm the values of literals sent in queries, for example, in WHERE clauses. Confirm that they do not or do not contain built-in time zones (they do not look, as they are, from the information you provide).
Get the exact SQL text executed by the server, including any placeholder values. Try to run the same queries directly in the database using the psql command-line tool. In doing so, set timezone to the same value as in Django (if any). You can then experiment with changing the various values until you get a working request, and work back to get your Django code.
TIP. You can get postgresql to register all statements by setting the log_statement configuration log_statement to all .
You probably do not want to conduct such testing on your real production system. I recommend that you reconfigure your dev system so that it uses Postgresql as its backend and then reproduces the problem you are having.
This brings me to my final piece of advice: when possible, use the same database system in your development system as you use in production . Even given the framework that should hide database details from the application developer, there are often big differences between how various SQL databases work.