id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34699	Filtering on annotated TruncSecond expression gives unexpected result.	Stefan	Wes P.	"With a non-UTC time zone:

{{{#!python
  TIME_ZONE = 'Europe/Berlin'
  }}}

and a simply query like so

{{{#!python
  from django.db.models.functions import TruncSecond
  from django.utils import timezone

  book = Book.objects.get(id=2)
  now = timezone.now()
  book.published = now
  book.save()
  Book.objects.annotate(_published_trunc=TruncSecond('published')).filter(id=2, _published_trunc__lte=now)
  }}}

The result is empty; I have simply filtered `now` against a second-trunced version of `now` so I would expect a result.

However under the hood the `_published_now` column is converted to a naive timestamp using `AT TIME ZONE 'Europe/Berlin'` and is thus a naive timestamp 2 hours ''ahead'' of UTC.

{{{#!sql
SELECT ""book"".""id"",
       ""book"".""published"",
       DATE_TRUNC('second', ""book"".""published"" AT TIME ZONE 'Europe/Berlin') AS ""_published_trunc""
FROM ""book""
WHERE (""book"".""id"" = 2 AND
       DATE_TRUNC('second', ""book"".""_published_trunc"" AT TIME ZONE 'Europe/Berlin') <=
       '2023-07-04 11:59:00+02:00'::timestamptz)
}}}


The filter compares a naive timestamp to an aware one, but assumes the LHS naive timestamp is a UTC timestamp - which it is not, it is Berlin time.

**Workaround**

1) Use `TruncSecond(now)` in the filter so the compared naive timestamps are the same.

2) Use `_published_trunc=TruncSecond('published', tzinfo=datetime.timezone.utc)` - I don't like this though. It's not clear without a comment why the tzinfo is needed ''and'' it assumes the database will compare timezones using UTC.
"	Cleanup/optimization	assigned	Database layer (models, ORM)	dev	Normal			Aymeric Augustin Simon Charette David Sanders	Accepted	1	0	0	1	0	0