Using dates in table_query() method

[khurram (Khurram Shahzad)]

Hi all,

I have overridden the table_query() method of my model. I am using create_date in where clause. Since the type of create_date is ‘timestamp(6) without timezone’, how can I have the timezone adjustment applied on my queries.

For example, for I can see a record having create_date “2018-09-09 19:03:21.944918” in database. The actual date with timezone ‘GMT+5’ will be '“2018-10-09 00:03:21.944918”. Now, when we search for records having create_date ‘2018-09-09’, this record should not appear; but it is appearing in our search results. This is probably due to the reason that the timezone is not applied on this column. How can I update the where clause to apply timezone.

I am using create_date as follows:
where &= join00.right.create_date <= Transaction().context[‘end_date’]

But, I want something like:
where &= join00.right.create_date + interval ‘5 hours’ <= Transaction().context[‘end_date’]

[ced (Cédric Krier)]

I think it would be better to compare against a datetime instead of a date.
datetime from the client are automatically converted into UTC and so you will work with a single timezone.

[khurram (Khurram Shahzad)]

Sorry for the miscommunication. I am already comparing create_date against a datetime. Actually we are searching for records having create_date ‘2018-09-09 00:00:00’.

[ced (Cédric Krier)]

Then you should use a datetime in UTC. If it comes from the client it will automatically be converted in UTC otherwise you as developer is responsible to always use UTC datetime.