Chaining django queries on dates not working as expected

Question

I have a Django 4.2 app (mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))) with a model Album. The Album model has a DateTimeField called date.

I run a query to select albums by the year in launch_date:

>>> launch_date = datetime(2014, 5, 10)
>>> albums = Album.objects.filter(date__year=launch_date.year)
>>> albums
<QuerySet [<Album: 732 - September 30, 2014>, <Album: 734 - February 6, 2009>, <Album: 735 - March 29, 2010>, <Album: 736 - April 5, 2010>, <Album: 738 - May 20, 2014>]>
>>> for a in albums:
        print(a.album_id, a.date.year, a.date.month)
732 2014 10
734 2014 10
735 2014 10
736 2014 10
738 2014 5

Which is what I expected.

However, when I run this query to further filter the albums by month, it fails. I expected the query to return the one album with album_id = 738:

>>> albums.filter(date__month=launch_date.month)
<QuerySet []>

These chained filters do not produce the results I expected, either:

>>> Album.objects.filter(date__year=2014, date__month=5)
<QuerySet []>
>>> Album.objects.filter(date__year=launch_date.year).filter(date__month=launch_date.month)
<QuerySet []>

What am I doing wrong?

A short update. I ran these queries with these results, which do not make a lot of sense:

>>> Album.objects.filter(date__startswith='2014-05')
<QuerySet [<Album: 738 - May 20, 2014>]>
>>>> Album.objects.filter(date__contains='2014-05')
<QuerySet [<Album: 738 - May 20, 2014>]>
>>> Album.objects.filter(date__year=2014).filter(date__month=5)
<QuerySet []>

I enabled showing sql used in queries, and this is what I get.

query1 = Album.objects.filter(date__year=2014)
logger.debug("query1=%s" % query1)
query1=<QuerySet [<Album: 732 - September 30, 2014>, <Album: 738 - May 20, 2014>]>

This is the SQL generated for query 1:

SELECT `Album`.`album_id`, `Album`.`title`, `Album`.`description`, `Album`.`date`, `Album`.`cover_image_id`, `Album`.`sort_option`, `Album`.`sort_order`, `Album`.`created`, `Album`.`updated`, `Album`.`is_published` FROM `Album` WHERE `Album`.`date` BETWEEN '2014-01-01 07:00:00' AND '2015-01-01 06:59:59.999999' ORDER BY `Album`.`album_id` ASC LIMIT 21; args=('2014-01-01 07:00:00', '2015-01-01 06:59:59.999999')


query2 = Album.objects.filter(date__year=launch_date.year).filter(date__month=5)
logger.debug("query2=%s" % query2)
query2=<QuerySet []>

The SQL for query2:
    
SELECT `Album`.`album_id`, `Album`.`title`, `Album`.`description`, `Album`.`date`, `Album`.`cover_image_id`, `Album`.`sort_option`, `Album`.`sort_order`, `Album`.`created`, `Album`.`updated`, `Album`.`is_published` FROM `Album` WHERE (`Album`.`date` BETWEEN '2014-01-01 07:00:00' AND '2015-01-01 06:59:59.999999' AND EXTRACT(MONTH FROM CONVERT_TZ(`Album`.`date`, 'UTC', 'America/Phoenix')) = 5) ORDER BY `Album`.`album_id` ASC LIMIT 21; args=('2014-01-01 07:00:00', '2015-01-01 06:59:59.999999', 5)

In case it helps, here is the Album model:

class ViewsMixin(models.Model):
    views = GenericRelation('viewcount.Views')
    recent_views = GenericRelation('viewcount.RecentViews')

    class Meta:
        abstract = True

def get_default_date():
    #return datetime(1970, 1, 1)
    return datetime.now()

class PublishedAlbumsManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(is_published=True)
 
class Album(ViewsMixin):
    def get_default_cover_image():
        default_image_id = Image.objects.get(image_id=ImageTags.objects.get(tag_id_id=Tags.objects.get(tag_name="Default Image")).image_id_id).image_id
        return default_image_id    
    
    album_id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    date = models.DateTimeField(default=get_default_date)   
    cover_image = models.ForeignKey(Image, on_delete=models.CASCADE, default=get_default_cover_image)
    tags = models.ManyToManyField(Tags, blank=True)
    sort_option = models.CharField(choices=settings.ALBUM_SORT_OPTIONS, default='creation_date', max_length=16)
    sort_order = models.CharField(choices=settings.ALBUM_SORT_ORDER, default='ascending', max_length=16)
    created = models.DateTimeField(auto_now_add=True, editable=False, verbose_name="date created")
    updated = models.DateTimeField(auto_now=True, editable=False, verbose_name="last update")
    is_published = models.BooleanField(default=False)
    history = HistoricalRecords()
    
    objects = models.Manager()
    published = PublishedAlbumsManager()
    
    def __str__(self):
        #return self.title
        return "%s - %s" % (self.album_id, self.title)

Regarding timezone issues, I checked mysql server and my Ubuntu 22.04 system. This is what I found.

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

mark@piranha:~$ ls -alh /etc/localtime
lrwxrwxrwx 1 root root 35 Jan 25 14:41 /etc/localtime -> /usr/share/zoneinfo/America/Phoenix

Phoenix is GMT-7 with no DST.