#30224: Mysql Datetime value is string instead of datetime object
-------------------------------------+-------------------------------------
Reporter: Martin Kuhn | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: Mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Martin Kuhn):
Ok, I managed to narrow down the problem. My Query is using annotations to
use the aggregation function MIN.
{{{
Project.objects \
.prefetch_related('buyout_set') \
.prefetch_related('teammember_set') \
.annotate(min_expiry_date=Min('buyout__expiry_date',
filter=Q(buyout__is_archived=False,
buyout__is_deleted=False))
) \
.filter(Q(min_expiry_date__gte=(now + expiryInterval))
& Q(min_expiry_date__lte=(now + expiryInterval +
timedelta(days=1))))
}}}
I tested the generated query below using `mysqlclient` and found that it
does return the annotated value as a string. The problem is a custom
annotation "MIN(Date)". Probably mysqlclient has no type info for this
custom value, since it could be any/multiple types. (E.g. using an IF
statement.)
However this auto generated query could be rewritten to use CAST(
"annotation" as DateTime).
The resulting query is
----
{{{
SELECT
`advertisement_project`.`id`,
`advertisement_project`.`created_on`,
`advertisement_project`.`modified_on`,
`advertisement_project`.`is_deleted`,
`advertisement_project`.`created_by_id`,
`advertisement_project`.`modified_by_id`,
`advertisement_project`.`name`,
`advertisement_project`.`code`,
`advertisement_project`.`spot_length`,
`advertisement_project`.`eta`,
`advertisement_project`.`spot_id`,
`advertisement_project`.`spot_id_suffix`,
`advertisement_project`.`folder_id`,
`advertisement_project`.`system_id`,
`advertisement_project`.`status`,
`advertisement_project`.`type`,
`advertisement_project`.`ad_type`,
`advertisement_project`.`parent_project_id`,
`advertisement_project`.`original_project_id`,
`advertisement_project`.`territory_id`,
`advertisement_project`.`language_id`,
`advertisement_project`.`campaign_id`,
`advertisement_project`.`preview_id`,
`advertisement_project`.`links_id`,
`advertisement_project`.`producer_names`,
MIN(CASE
WHEN
(`buyout_buyout`.`is_archived` = 0
AND `buyout_buyout`.`is_deleted` = 0)
THEN
`buyout_buyout`.`expiry_date`
ELSE NULL
END) AS `minExpiryDate`
FROM
`advertisement_project`
LEFT OUTER JOIN
`buyout_buyout` ON (`advertisement_project`.`id` =
`buyout_buyout`.`project_id`)
GROUP BY `advertisement_project`.`id`
HAVING (MIN(CASE
WHEN
(`buyout_buyout`.`is_archived` = 0
AND `buyout_buyout`.`is_deleted` = 0)
THEN
`buyout_buyout`.`expiry_date`
ELSE NULL
END) >= '2019-03-01 00:00:00'
AND MIN(CASE
WHEN
(`buyout_buyout`.`is_archived` = 0
AND `buyout_buyout`.`is_deleted` = 0)
THEN
`buyout_buyout`.`expiry_date`
ELSE NULL
END) <= '2019-03-02 00:00:00')
ORDER BY NULL;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30224#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.685849d8d4c59bed37021dd4bb0182cb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
