Debian Codegraph @

tests/db_functions/datetime/test_extract_trunc.py

raw
1 import zoneinfo
2 from datetime import datetime, timedelta
3 from datetime import timezone as datetime_timezone
4
5 from django.conf import settings
6 from django.db import DataError, OperationalError
7 from django.db.models import (
8 DateField,
9 DateTimeField,
10 F,
11 IntegerField,
12 Max,
13 OuterRef,
14 Subquery,
15 TimeField,
16 )
17 from django.db.models.functions import (
18 Extract,
19 ExtractDay,
20 ExtractHour,
21 ExtractIsoWeekDay,
22 ExtractIsoYear,
23 ExtractMinute,
24 ExtractMonth,
25 ExtractQuarter,
26 ExtractSecond,
27 ExtractWeek,
28 ExtractWeekDay,
29 ExtractYear,
30 Trunc,
31 TruncDate,
32 TruncDay,
33 TruncHour,
34 TruncMinute,
35 TruncMonth,
36 TruncQuarter,
37 TruncSecond,
38 TruncTime,
39 TruncWeek,
40 TruncYear,
41 )
42 from django.test import (
43 TestCase,
44 override_settings,
45 skipIfDBFeature,
46 skipUnlessDBFeature,
47 )
48 from django.utils import timezone
49
50 from ..models import Author, DTModel, Fan
51
52
53 def truncate_to(value, kind, tzinfo=None):
54 # Convert to target timezone before truncation
55 if tzinfo is not None:
56 value = value.astimezone(tzinfo)
57
58 def truncate(value, kind):
59 if kind == "second":
60 return value.replace(microsecond=0)
61 if kind == "minute":
62 return value.replace(second=0, microsecond=0)
63 if kind == "hour":
64 return value.replace(minute=0, second=0, microsecond=0)
65 if kind == "day":
66 if isinstance(value, datetime):
67 return value.replace(hour=0, minute=0, second=0, microsecond=0)
68 return value
69 if kind == "week":
70 if isinstance(value, datetime):
71 return (value - timedelta(days=value.weekday())).replace(
72 hour=0, minute=0, second=0, microsecond=0
73 )
74 return value - timedelta(days=value.weekday())
75 if kind == "month":
76 if isinstance(value, datetime):
77 return value.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
78 return value.replace(day=1)
79 if kind == "quarter":
80 month_in_quarter = value.month - (value.month - 1) % 3
81 if isinstance(value, datetime):
82 return value.replace(
83 month=month_in_quarter,
84 day=1,
85 hour=0,
86 minute=0,
87 second=0,
88 microsecond=0,
89 )
90 return value.replace(month=month_in_quarter, day=1)
91 # otherwise, truncate to year
92 if isinstance(value, datetime):
93 return value.replace(
94 month=1, day=1, hour=0, minute=0, second=0, microsecond=0
95 )
96 return value.replace(month=1, day=1)
97
98 value = truncate(value, kind)
99 if tzinfo is not None:
100 # If there was a daylight saving transition, then reset the timezone.
101 value = timezone.make_aware(value.replace(tzinfo=None), tzinfo)
102 return value
103
104
105 @override_settings(USE_TZ=False)
106 class DateFunctionTests(TestCase):
107 def create_model(self, start_datetime, end_datetime):
108 return DTModel.objects.create(
109 name=start_datetime.isoformat() if start_datetime else "None",
110 start_datetime=start_datetime,
111 end_datetime=end_datetime,
112 start_date=start_datetime.date() if start_datetime else None,
113 end_date=end_datetime.date() if end_datetime else None,
114 start_time=start_datetime.time() if start_datetime else None,
115 end_time=end_datetime.time() if end_datetime else None,
116 duration=(
117 (end_datetime - start_datetime)
118 if start_datetime and end_datetime
119 else None
120 ),
121 )
122
123 def test_extract_year_exact_lookup(self):
124 """
125 Extract year uses a BETWEEN filter to compare the year to allow indexes
126 to be used.
127 """
128 start_datetime = datetime(2015, 6, 15, 14, 10)
129 end_datetime = datetime(2016, 6, 15, 14, 10)
130 if settings.USE_TZ:
131 start_datetime = timezone.make_aware(start_datetime)
132 end_datetime = timezone.make_aware(end_datetime)
133 self.create_model(start_datetime, end_datetime)
134 self.create_model(end_datetime, start_datetime)
135
136 for lookup in ("year", "iso_year"):
137 with self.subTest(lookup):
138 qs = DTModel.objects.filter(
139 **{"start_datetime__%s__exact" % lookup: 2015}
140 )
141 self.assertEqual(qs.count(), 1)
142 query_string = str(qs.query).lower()
143 self.assertEqual(query_string.count(" between "), 1)
144 self.assertEqual(query_string.count("extract"), 0)
145 # exact is implied and should be the same
146 qs = DTModel.objects.filter(**{"start_datetime__%s" % lookup: 2015})
147 self.assertEqual(qs.count(), 1)
148 query_string = str(qs.query).lower()
149 self.assertEqual(query_string.count(" between "), 1)
150 self.assertEqual(query_string.count("extract"), 0)
151 # date and datetime fields should behave the same
152 qs = DTModel.objects.filter(**{"start_date__%s" % lookup: 2015})
153 self.assertEqual(qs.count(), 1)
154 query_string = str(qs.query).lower()
155 self.assertEqual(query_string.count(" between "), 1)
156 self.assertEqual(query_string.count("extract"), 0)
157 # an expression rhs cannot use the between optimization.
158 qs = DTModel.objects.annotate(
159 start_year=ExtractYear("start_datetime"),
160 ).filter(end_datetime__year=F("start_year") + 1)
161 self.assertEqual(qs.count(), 1)
162 query_string = str(qs.query).lower()
163 self.assertEqual(query_string.count(" between "), 0)
164 self.assertEqual(query_string.count("extract"), 3)
165
166 def test_extract_year_greaterthan_lookup(self):
167 start_datetime = datetime(2015, 6, 15, 14, 10)
168 end_datetime = datetime(2016, 6, 15, 14, 10)
169 if settings.USE_TZ:
170 start_datetime = timezone.make_aware(start_datetime)
171 end_datetime = timezone.make_aware(end_datetime)
172 self.create_model(start_datetime, end_datetime)
173 self.create_model(end_datetime, start_datetime)
174
175 for lookup in ("year", "iso_year"):
176 with self.subTest(lookup):
177 qs = DTModel.objects.filter(**{"start_datetime__%s__gt" % lookup: 2015})
178 self.assertEqual(qs.count(), 1)
179 self.assertEqual(str(qs.query).lower().count("extract"), 0)
180 qs = DTModel.objects.filter(
181 **{"start_datetime__%s__gte" % lookup: 2015}
182 )
183 self.assertEqual(qs.count(), 2)
184 self.assertEqual(str(qs.query).lower().count("extract"), 0)
185 qs = DTModel.objects.annotate(
186 start_year=ExtractYear("start_datetime"),
187 ).filter(**{"end_datetime__%s__gte" % lookup: F("start_year")})
188 self.assertEqual(qs.count(), 1)
189 self.assertGreaterEqual(str(qs.query).lower().count("extract"), 2)
190
191 def test_extract_year_lessthan_lookup(self):
192 start_datetime = datetime(2015, 6, 15, 14, 10)
193 end_datetime = datetime(2016, 6, 15, 14, 10)
194 if settings.USE_TZ:
195 start_datetime = timezone.make_aware(start_datetime)
196 end_datetime = timezone.make_aware(end_datetime)
197 self.create_model(start_datetime, end_datetime)
198 self.create_model(end_datetime, start_datetime)
199
200 for lookup in ("year", "iso_year"):
201 with self.subTest(lookup):
202 qs = DTModel.objects.filter(**{"start_datetime__%s__lt" % lookup: 2016})
203 self.assertEqual(qs.count(), 1)
204 self.assertEqual(str(qs.query).count("extract"), 0)
205 qs = DTModel.objects.filter(
206 **{"start_datetime__%s__lte" % lookup: 2016}
207 )
208 self.assertEqual(qs.count(), 2)
209 self.assertEqual(str(qs.query).count("extract"), 0)
210 qs = DTModel.objects.annotate(
211 end_year=ExtractYear("end_datetime"),
212 ).filter(**{"start_datetime__%s__lte" % lookup: F("end_year")})
213 self.assertEqual(qs.count(), 1)
214 self.assertGreaterEqual(str(qs.query).lower().count("extract"), 2)
215
216 def test_extract_lookup_name_sql_injection(self):
217 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
218 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
219 if settings.USE_TZ:
220 start_datetime = timezone.make_aware(start_datetime)
221 end_datetime = timezone.make_aware(end_datetime)
222 self.create_model(start_datetime, end_datetime)
223 self.create_model(end_datetime, start_datetime)
224
225 with self.assertRaises((OperationalError, ValueError)):
226 DTModel.objects.filter(
227 start_datetime__year=Extract(
228 "start_datetime", "day' FROM start_datetime)) OR 1=1;--"
229 )
230 ).exists()
231
232 def test_extract_func(self):
233 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
234 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
235 if settings.USE_TZ:
236 start_datetime = timezone.make_aware(start_datetime)
237 end_datetime = timezone.make_aware(end_datetime)
238 self.create_model(start_datetime, end_datetime)
239 self.create_model(end_datetime, start_datetime)
240
241 with self.assertRaisesMessage(ValueError, "lookup_name must be provided"):
242 Extract("start_datetime")
243
244 msg = (
245 "Extract input expression must be DateField, DateTimeField, TimeField, or "
246 "DurationField."
247 )
248 with self.assertRaisesMessage(ValueError, msg):
249 list(DTModel.objects.annotate(extracted=Extract("name", "hour")))
250
251 with self.assertRaisesMessage(
252 ValueError,
253 "Cannot extract time component 'second' from DateField 'start_date'.",
254 ):
255 list(DTModel.objects.annotate(extracted=Extract("start_date", "second")))
256
257 self.assertQuerySetEqual(
258 DTModel.objects.annotate(
259 extracted=Extract("start_datetime", "year")
260 ).order_by("start_datetime"),
261 [(start_datetime, start_datetime.year), (end_datetime, end_datetime.year)],
262 lambda m: (m.start_datetime, m.extracted),
263 )
264 self.assertQuerySetEqual(
265 DTModel.objects.annotate(
266 extracted=Extract("start_datetime", "quarter")
267 ).order_by("start_datetime"),
268 [(start_datetime, 2), (end_datetime, 2)],
269 lambda m: (m.start_datetime, m.extracted),
270 )
271 self.assertQuerySetEqual(
272 DTModel.objects.annotate(
273 extracted=Extract("start_datetime", "month")
274 ).order_by("start_datetime"),
275 [
276 (start_datetime, start_datetime.month),
277 (end_datetime, end_datetime.month),
278 ],
279 lambda m: (m.start_datetime, m.extracted),
280 )
281 self.assertQuerySetEqual(
282 DTModel.objects.annotate(
283 extracted=Extract("start_datetime", "day")
284 ).order_by("start_datetime"),
285 [(start_datetime, start_datetime.day), (end_datetime, end_datetime.day)],
286 lambda m: (m.start_datetime, m.extracted),
287 )
288 self.assertQuerySetEqual(
289 DTModel.objects.annotate(
290 extracted=Extract("start_datetime", "week")
291 ).order_by("start_datetime"),
292 [(start_datetime, 25), (end_datetime, 24)],
293 lambda m: (m.start_datetime, m.extracted),
294 )
295 self.assertQuerySetEqual(
296 DTModel.objects.annotate(
297 extracted=Extract("start_datetime", "week_day")
298 ).order_by("start_datetime"),
299 [
300 (start_datetime, (start_datetime.isoweekday() % 7) + 1),
301 (end_datetime, (end_datetime.isoweekday() % 7) + 1),
302 ],
303 lambda m: (m.start_datetime, m.extracted),
304 )
305 self.assertQuerySetEqual(
306 DTModel.objects.annotate(
307 extracted=Extract("start_datetime", "iso_week_day"),
308 ).order_by("start_datetime"),
309 [
310 (start_datetime, start_datetime.isoweekday()),
311 (end_datetime, end_datetime.isoweekday()),
312 ],
313 lambda m: (m.start_datetime, m.extracted),
314 )
315 self.assertQuerySetEqual(
316 DTModel.objects.annotate(
317 extracted=Extract("start_datetime", "hour")
318 ).order_by("start_datetime"),
319 [(start_datetime, start_datetime.hour), (end_datetime, end_datetime.hour)],
320 lambda m: (m.start_datetime, m.extracted),
321 )
322 self.assertQuerySetEqual(
323 DTModel.objects.annotate(
324 extracted=Extract("start_datetime", "minute")
325 ).order_by("start_datetime"),
326 [
327 (start_datetime, start_datetime.minute),
328 (end_datetime, end_datetime.minute),
329 ],
330 lambda m: (m.start_datetime, m.extracted),
331 )
332 self.assertQuerySetEqual(
333 DTModel.objects.annotate(
334 extracted=Extract("start_datetime", "second")
335 ).order_by("start_datetime"),
336 [
337 (start_datetime, start_datetime.second),
338 (end_datetime, end_datetime.second),
339 ],
340 lambda m: (m.start_datetime, m.extracted),
341 )
342 self.assertEqual(
343 DTModel.objects.filter(
344 start_datetime__year=Extract("start_datetime", "year")
345 ).count(),
346 2,
347 )
348 self.assertEqual(
349 DTModel.objects.filter(
350 start_datetime__hour=Extract("start_datetime", "hour")
351 ).count(),
352 2,
353 )
354 self.assertEqual(
355 DTModel.objects.filter(
356 start_date__month=Extract("start_date", "month")
357 ).count(),
358 2,
359 )
360 self.assertEqual(
361 DTModel.objects.filter(
362 start_time__hour=Extract("start_time", "hour")
363 ).count(),
364 2,
365 )
366
367 def test_extract_none(self):
368 self.create_model(None, None)
369 for t in (
370 Extract("start_datetime", "year"),
371 Extract("start_date", "year"),
372 Extract("start_time", "hour"),
373 ):
374 with self.subTest(t):
375 self.assertIsNone(
376 DTModel.objects.annotate(extracted=t).first().extracted
377 )
378
379 def test_extract_outerref_validation(self):
380 inner_qs = DTModel.objects.filter(name=ExtractMonth(OuterRef("name")))
381 msg = (
382 "Extract input expression must be DateField, DateTimeField, "
383 "TimeField, or DurationField."
384 )
385 with self.assertRaisesMessage(ValueError, msg):
386 DTModel.objects.annotate(related_name=Subquery(inner_qs.values("name")[:1]))
387
388 @skipUnlessDBFeature("has_native_duration_field")
389 def test_extract_duration(self):
390 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
391 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
392 if settings.USE_TZ:
393 start_datetime = timezone.make_aware(start_datetime)
394 end_datetime = timezone.make_aware(end_datetime)
395 self.create_model(start_datetime, end_datetime)
396 self.create_model(end_datetime, start_datetime)
397 self.assertQuerySetEqual(
398 DTModel.objects.annotate(extracted=Extract("duration", "second")).order_by(
399 "start_datetime"
400 ),
401 [
402 (start_datetime, (end_datetime - start_datetime).seconds % 60),
403 (end_datetime, (start_datetime - end_datetime).seconds % 60),
404 ],
405 lambda m: (m.start_datetime, m.extracted),
406 )
407 self.assertEqual(
408 DTModel.objects.annotate(
409 duration_days=Extract("duration", "day"),
410 )
411 .filter(duration_days__gt=200)
412 .count(),
413 1,
414 )
415
416 @skipIfDBFeature("has_native_duration_field")
417 def test_extract_duration_without_native_duration_field(self):
418 msg = "Extract requires native DurationField database support."
419 with self.assertRaisesMessage(ValueError, msg):
420 list(DTModel.objects.annotate(extracted=Extract("duration", "second")))
421
422 def test_extract_duration_unsupported_lookups(self):
423 msg = "Cannot extract component '%s' from DurationField 'duration'."
424 for lookup in (
425 "year",
426 "iso_year",
427 "month",
428 "week",
429 "week_day",
430 "iso_week_day",
431 "quarter",
432 ):
433 with self.subTest(lookup):
434 with self.assertRaisesMessage(ValueError, msg % lookup):
435 DTModel.objects.annotate(extracted=Extract("duration", lookup))
436
437 def test_extract_year_func(self):
438 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
439 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
440 if settings.USE_TZ:
441 start_datetime = timezone.make_aware(start_datetime)
442 end_datetime = timezone.make_aware(end_datetime)
443 self.create_model(start_datetime, end_datetime)
444 self.create_model(end_datetime, start_datetime)
445 self.assertQuerySetEqual(
446 DTModel.objects.annotate(extracted=ExtractYear("start_datetime")).order_by(
447 "start_datetime"
448 ),
449 [(start_datetime, start_datetime.year), (end_datetime, end_datetime.year)],
450 lambda m: (m.start_datetime, m.extracted),
451 )
452 self.assertQuerySetEqual(
453 DTModel.objects.annotate(extracted=ExtractYear("start_date")).order_by(
454 "start_datetime"
455 ),
456 [(start_datetime, start_datetime.year), (end_datetime, end_datetime.year)],
457 lambda m: (m.start_datetime, m.extracted),
458 )
459 self.assertEqual(
460 DTModel.objects.filter(
461 start_datetime__year=ExtractYear("start_datetime")
462 ).count(),
463 2,
464 )
465
466 def test_extract_iso_year_func(self):
467 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
468 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
469 if settings.USE_TZ:
470 start_datetime = timezone.make_aware(start_datetime)
471 end_datetime = timezone.make_aware(end_datetime)
472 self.create_model(start_datetime, end_datetime)
473 self.create_model(end_datetime, start_datetime)
474 self.assertQuerySetEqual(
475 DTModel.objects.annotate(
476 extracted=ExtractIsoYear("start_datetime")
477 ).order_by("start_datetime"),
478 [(start_datetime, start_datetime.year), (end_datetime, end_datetime.year)],
479 lambda m: (m.start_datetime, m.extracted),
480 )
481 self.assertQuerySetEqual(
482 DTModel.objects.annotate(extracted=ExtractIsoYear("start_date")).order_by(
483 "start_datetime"
484 ),
485 [(start_datetime, start_datetime.year), (end_datetime, end_datetime.year)],
486 lambda m: (m.start_datetime, m.extracted),
487 )
488 # Both dates are from the same week year.
489 self.assertEqual(
490 DTModel.objects.filter(
491 start_datetime__iso_year=ExtractIsoYear("start_datetime")
492 ).count(),
493 2,
494 )
495
496 def test_extract_iso_year_func_boundaries(self):
497 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
498 if settings.USE_TZ:
499 end_datetime = timezone.make_aware(end_datetime)
500 week_52_day_2014 = datetime(2014, 12, 27, 13, 0) # Sunday
501 week_1_day_2014_2015 = datetime(2014, 12, 31, 13, 0) # Wednesday
502 week_53_day_2015 = datetime(2015, 12, 31, 13, 0) # Thursday
503 if settings.USE_TZ:
504 week_1_day_2014_2015 = timezone.make_aware(week_1_day_2014_2015)
505 week_52_day_2014 = timezone.make_aware(week_52_day_2014)
506 week_53_day_2015 = timezone.make_aware(week_53_day_2015)
507 days = [week_52_day_2014, week_1_day_2014_2015, week_53_day_2015]
508 obj_1_iso_2014 = self.create_model(week_52_day_2014, end_datetime)
509 obj_1_iso_2015 = self.create_model(week_1_day_2014_2015, end_datetime)
510 obj_2_iso_2015 = self.create_model(week_53_day_2015, end_datetime)
511 qs = (
512 DTModel.objects.filter(start_datetime__in=days)
513 .annotate(
514 extracted=ExtractIsoYear("start_datetime"),
515 )
516 .order_by("start_datetime")
517 )
518 self.assertQuerySetEqual(
519 qs,
520 [
521 (week_52_day_2014, 2014),
522 (week_1_day_2014_2015, 2015),
523 (week_53_day_2015, 2015),
524 ],
525 lambda m: (m.start_datetime, m.extracted),
526 )
527
528 qs = DTModel.objects.filter(
529 start_datetime__iso_year=2015,
530 ).order_by("start_datetime")
531 self.assertSequenceEqual(qs, [obj_1_iso_2015, obj_2_iso_2015])
532 qs = DTModel.objects.filter(
533 start_datetime__iso_year__gt=2014,
534 ).order_by("start_datetime")
535 self.assertSequenceEqual(qs, [obj_1_iso_2015, obj_2_iso_2015])
536 qs = DTModel.objects.filter(
537 start_datetime__iso_year__lte=2014,
538 ).order_by("start_datetime")
539 self.assertSequenceEqual(qs, [obj_1_iso_2014])
540
541 def test_extract_month_func(self):
542 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
543 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
544 if settings.USE_TZ:
545 start_datetime = timezone.make_aware(start_datetime)
546 end_datetime = timezone.make_aware(end_datetime)
547 self.create_model(start_datetime, end_datetime)
548 self.create_model(end_datetime, start_datetime)
549 self.assertQuerySetEqual(
550 DTModel.objects.annotate(extracted=ExtractMonth("start_datetime")).order_by(
551 "start_datetime"
552 ),
553 [
554 (start_datetime, start_datetime.month),
555 (end_datetime, end_datetime.month),
556 ],
557 lambda m: (m.start_datetime, m.extracted),
558 )
559 self.assertQuerySetEqual(
560 DTModel.objects.annotate(extracted=ExtractMonth("start_date")).order_by(
561 "start_datetime"
562 ),
563 [
564 (start_datetime, start_datetime.month),
565 (end_datetime, end_datetime.month),
566 ],
567 lambda m: (m.start_datetime, m.extracted),
568 )
569 self.assertEqual(
570 DTModel.objects.filter(
571 start_datetime__month=ExtractMonth("start_datetime")
572 ).count(),
573 2,
574 )
575
576 def test_extract_day_func(self):
577 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
578 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
579 if settings.USE_TZ:
580 start_datetime = timezone.make_aware(start_datetime)
581 end_datetime = timezone.make_aware(end_datetime)
582 self.create_model(start_datetime, end_datetime)
583 self.create_model(end_datetime, start_datetime)
584 self.assertQuerySetEqual(
585 DTModel.objects.annotate(extracted=ExtractDay("start_datetime")).order_by(
586 "start_datetime"
587 ),
588 [(start_datetime, start_datetime.day), (end_datetime, end_datetime.day)],
589 lambda m: (m.start_datetime, m.extracted),
590 )
591 self.assertQuerySetEqual(
592 DTModel.objects.annotate(extracted=ExtractDay("start_date")).order_by(
593 "start_datetime"
594 ),
595 [(start_datetime, start_datetime.day), (end_datetime, end_datetime.day)],
596 lambda m: (m.start_datetime, m.extracted),
597 )
598 self.assertEqual(
599 DTModel.objects.filter(
600 start_datetime__day=ExtractDay("start_datetime")
601 ).count(),
602 2,
603 )
604
605 def test_extract_week_func(self):
606 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
607 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
608 if settings.USE_TZ:
609 start_datetime = timezone.make_aware(start_datetime)
610 end_datetime = timezone.make_aware(end_datetime)
611 self.create_model(start_datetime, end_datetime)
612 self.create_model(end_datetime, start_datetime)
613 self.assertQuerySetEqual(
614 DTModel.objects.annotate(extracted=ExtractWeek("start_datetime")).order_by(
615 "start_datetime"
616 ),
617 [(start_datetime, 25), (end_datetime, 24)],
618 lambda m: (m.start_datetime, m.extracted),
619 )
620 self.assertQuerySetEqual(
621 DTModel.objects.annotate(extracted=ExtractWeek("start_date")).order_by(
622 "start_datetime"
623 ),
624 [(start_datetime, 25), (end_datetime, 24)],
625 lambda m: (m.start_datetime, m.extracted),
626 )
627 # both dates are from the same week.
628 self.assertEqual(
629 DTModel.objects.filter(
630 start_datetime__week=ExtractWeek("start_datetime")
631 ).count(),
632 2,
633 )
634
635 def test_extract_quarter_func(self):
636 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
637 end_datetime = datetime(2016, 8, 15, 14, 10, 50, 123)
638 if settings.USE_TZ:
639 start_datetime = timezone.make_aware(start_datetime)
640 end_datetime = timezone.make_aware(end_datetime)
641 self.create_model(start_datetime, end_datetime)
642 self.create_model(end_datetime, start_datetime)
643 self.assertQuerySetEqual(
644 DTModel.objects.annotate(
645 extracted=ExtractQuarter("start_datetime")
646 ).order_by("start_datetime"),
647 [(start_datetime, 2), (end_datetime, 3)],
648 lambda m: (m.start_datetime, m.extracted),
649 )
650 self.assertQuerySetEqual(
651 DTModel.objects.annotate(extracted=ExtractQuarter("start_date")).order_by(
652 "start_datetime"
653 ),
654 [(start_datetime, 2), (end_datetime, 3)],
655 lambda m: (m.start_datetime, m.extracted),
656 )
657 self.assertEqual(
658 DTModel.objects.filter(
659 start_datetime__quarter=ExtractQuarter("start_datetime")
660 ).count(),
661 2,
662 )
663
664 def test_extract_quarter_func_boundaries(self):
665 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
666 if settings.USE_TZ:
667 end_datetime = timezone.make_aware(end_datetime)
668
669 last_quarter_2014 = datetime(2014, 12, 31, 13, 0)
670 first_quarter_2015 = datetime(2015, 1, 1, 13, 0)
671 if settings.USE_TZ:
672 last_quarter_2014 = timezone.make_aware(last_quarter_2014)
673 first_quarter_2015 = timezone.make_aware(first_quarter_2015)
674 dates = [last_quarter_2014, first_quarter_2015]
675 self.create_model(last_quarter_2014, end_datetime)
676 self.create_model(first_quarter_2015, end_datetime)
677 qs = (
678 DTModel.objects.filter(start_datetime__in=dates)
679 .annotate(
680 extracted=ExtractQuarter("start_datetime"),
681 )
682 .order_by("start_datetime")
683 )
684 self.assertQuerySetEqual(
685 qs,
686 [
687 (last_quarter_2014, 4),
688 (first_quarter_2015, 1),
689 ],
690 lambda m: (m.start_datetime, m.extracted),
691 )
692
693 def test_extract_week_func_boundaries(self):
694 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
695 if settings.USE_TZ:
696 end_datetime = timezone.make_aware(end_datetime)
697
698 week_52_day_2014 = datetime(2014, 12, 27, 13, 0) # Sunday
699 week_1_day_2014_2015 = datetime(2014, 12, 31, 13, 0) # Wednesday
700 week_53_day_2015 = datetime(2015, 12, 31, 13, 0) # Thursday
701 if settings.USE_TZ:
702 week_1_day_2014_2015 = timezone.make_aware(week_1_day_2014_2015)
703 week_52_day_2014 = timezone.make_aware(week_52_day_2014)
704 week_53_day_2015 = timezone.make_aware(week_53_day_2015)
705
706 days = [week_52_day_2014, week_1_day_2014_2015, week_53_day_2015]
707 self.create_model(week_53_day_2015, end_datetime)
708 self.create_model(week_52_day_2014, end_datetime)
709 self.create_model(week_1_day_2014_2015, end_datetime)
710 qs = (
711 DTModel.objects.filter(start_datetime__in=days)
712 .annotate(
713 extracted=ExtractWeek("start_datetime"),
714 )
715 .order_by("start_datetime")
716 )
717 self.assertQuerySetEqual(
718 qs,
719 [
720 (week_52_day_2014, 52),
721 (week_1_day_2014_2015, 1),
722 (week_53_day_2015, 53),
723 ],
724 lambda m: (m.start_datetime, m.extracted),
725 )
726
727 def test_extract_weekday_func(self):
728 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
729 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
730 if settings.USE_TZ:
731 start_datetime = timezone.make_aware(start_datetime)
732 end_datetime = timezone.make_aware(end_datetime)
733 self.create_model(start_datetime, end_datetime)
734 self.create_model(end_datetime, start_datetime)
735 self.assertQuerySetEqual(
736 DTModel.objects.annotate(
737 extracted=ExtractWeekDay("start_datetime")
738 ).order_by("start_datetime"),
739 [
740 (start_datetime, (start_datetime.isoweekday() % 7) + 1),
741 (end_datetime, (end_datetime.isoweekday() % 7) + 1),
742 ],
743 lambda m: (m.start_datetime, m.extracted),
744 )
745 self.assertQuerySetEqual(
746 DTModel.objects.annotate(extracted=ExtractWeekDay("start_date")).order_by(
747 "start_datetime"
748 ),
749 [
750 (start_datetime, (start_datetime.isoweekday() % 7) + 1),
751 (end_datetime, (end_datetime.isoweekday() % 7) + 1),
752 ],
753 lambda m: (m.start_datetime, m.extracted),
754 )
755 self.assertEqual(
756 DTModel.objects.filter(
757 start_datetime__week_day=ExtractWeekDay("start_datetime")
758 ).count(),
759 2,
760 )
761
762 def test_extract_iso_weekday_func(self):
763 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
764 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
765 if settings.USE_TZ:
766 start_datetime = timezone.make_aware(start_datetime)
767 end_datetime = timezone.make_aware(end_datetime)
768 self.create_model(start_datetime, end_datetime)
769 self.create_model(end_datetime, start_datetime)
770 self.assertQuerySetEqual(
771 DTModel.objects.annotate(
772 extracted=ExtractIsoWeekDay("start_datetime"),
773 ).order_by("start_datetime"),
774 [
775 (start_datetime, start_datetime.isoweekday()),
776 (end_datetime, end_datetime.isoweekday()),
777 ],
778 lambda m: (m.start_datetime, m.extracted),
779 )
780 self.assertQuerySetEqual(
781 DTModel.objects.annotate(
782 extracted=ExtractIsoWeekDay("start_date"),
783 ).order_by("start_datetime"),
784 [
785 (start_datetime, start_datetime.isoweekday()),
786 (end_datetime, end_datetime.isoweekday()),
787 ],
788 lambda m: (m.start_datetime, m.extracted),
789 )
790 self.assertEqual(
791 DTModel.objects.filter(
792 start_datetime__week_day=ExtractWeekDay("start_datetime"),
793 ).count(),
794 2,
795 )
796
797 def test_extract_hour_func(self):
798 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
799 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
800 if settings.USE_TZ:
801 start_datetime = timezone.make_aware(start_datetime)
802 end_datetime = timezone.make_aware(end_datetime)
803 self.create_model(start_datetime, end_datetime)
804 self.create_model(end_datetime, start_datetime)
805 self.assertQuerySetEqual(
806 DTModel.objects.annotate(extracted=ExtractHour("start_datetime")).order_by(
807 "start_datetime"
808 ),
809 [(start_datetime, start_datetime.hour), (end_datetime, end_datetime.hour)],
810 lambda m: (m.start_datetime, m.extracted),
811 )
812 self.assertQuerySetEqual(
813 DTModel.objects.annotate(extracted=ExtractHour("start_time")).order_by(
814 "start_datetime"
815 ),
816 [(start_datetime, start_datetime.hour), (end_datetime, end_datetime.hour)],
817 lambda m: (m.start_datetime, m.extracted),
818 )
819 self.assertEqual(
820 DTModel.objects.filter(
821 start_datetime__hour=ExtractHour("start_datetime")
822 ).count(),
823 2,
824 )
825
826 def test_extract_minute_func(self):
827 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
828 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
829 if settings.USE_TZ:
830 start_datetime = timezone.make_aware(start_datetime)
831 end_datetime = timezone.make_aware(end_datetime)
832 self.create_model(start_datetime, end_datetime)
833 self.create_model(end_datetime, start_datetime)
834 self.assertQuerySetEqual(
835 DTModel.objects.annotate(
836 extracted=ExtractMinute("start_datetime")
837 ).order_by("start_datetime"),
838 [
839 (start_datetime, start_datetime.minute),
840 (end_datetime, end_datetime.minute),
841 ],
842 lambda m: (m.start_datetime, m.extracted),
843 )
844 self.assertQuerySetEqual(
845 DTModel.objects.annotate(extracted=ExtractMinute("start_time")).order_by(
846 "start_datetime"
847 ),
848 [
849 (start_datetime, start_datetime.minute),
850 (end_datetime, end_datetime.minute),
851 ],
852 lambda m: (m.start_datetime, m.extracted),
853 )
854 self.assertEqual(
855 DTModel.objects.filter(
856 start_datetime__minute=ExtractMinute("start_datetime")
857 ).count(),
858 2,
859 )
860
861 def test_extract_second_func(self):
862 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
863 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
864 if settings.USE_TZ:
865 start_datetime = timezone.make_aware(start_datetime)
866 end_datetime = timezone.make_aware(end_datetime)
867 self.create_model(start_datetime, end_datetime)
868 self.create_model(end_datetime, start_datetime)
869 self.assertQuerySetEqual(
870 DTModel.objects.annotate(
871 extracted=ExtractSecond("start_datetime")
872 ).order_by("start_datetime"),
873 [
874 (start_datetime, start_datetime.second),
875 (end_datetime, end_datetime.second),
876 ],
877 lambda m: (m.start_datetime, m.extracted),
878 )
879 self.assertQuerySetEqual(
880 DTModel.objects.annotate(extracted=ExtractSecond("start_time")).order_by(
881 "start_datetime"
882 ),
883 [
884 (start_datetime, start_datetime.second),
885 (end_datetime, end_datetime.second),
886 ],
887 lambda m: (m.start_datetime, m.extracted),
888 )
889 self.assertEqual(
890 DTModel.objects.filter(
891 start_datetime__second=ExtractSecond("start_datetime")
892 ).count(),
893 2,
894 )
895
896 def test_extract_second_func_no_fractional(self):
897 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
898 end_datetime = datetime(2016, 6, 15, 14, 30, 50, 783)
899 if settings.USE_TZ:
900 start_datetime = timezone.make_aware(start_datetime)
901 end_datetime = timezone.make_aware(end_datetime)
902 obj = self.create_model(start_datetime, end_datetime)
903 self.assertSequenceEqual(
904 DTModel.objects.filter(start_datetime__second=F("end_datetime__second")),
905 [obj],
906 )
907 self.assertSequenceEqual(
908 DTModel.objects.filter(start_time__second=F("end_time__second")),
909 [obj],
910 )
911
912 def test_trunc_lookup_name_sql_injection(self):
913 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
914 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
915 if settings.USE_TZ:
916 start_datetime = timezone.make_aware(start_datetime)
917 end_datetime = timezone.make_aware(end_datetime)
918 self.create_model(start_datetime, end_datetime)
919 self.create_model(end_datetime, start_datetime)
920 # Database backends raise an exception or don't return any results.
921 try:
922 exists = DTModel.objects.filter(
923 start_datetime__date=Trunc(
924 "start_datetime",
925 "year', start_datetime)) OR 1=1;--",
926 )
927 ).exists()
928 except (DataError, OperationalError):
929 pass
930 else:
931 self.assertIs(exists, False)
932
933 def test_trunc_func(self):
934 start_datetime = datetime(999, 6, 15, 14, 30, 50, 321)
935 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
936 if settings.USE_TZ:
937 start_datetime = timezone.make_aware(start_datetime)
938 end_datetime = timezone.make_aware(end_datetime)
939 self.create_model(start_datetime, end_datetime)
940 self.create_model(end_datetime, start_datetime)
941
942 def assertDatetimeKind(kind):
943 truncated_start = truncate_to(start_datetime, kind)
944 truncated_end = truncate_to(end_datetime, kind)
945 queryset = DTModel.objects.annotate(
946 truncated=Trunc("start_datetime", kind, output_field=DateTimeField())
947 ).order_by("start_datetime")
948 self.assertSequenceEqual(
949 queryset.values_list("start_datetime", "truncated"),
950 [
951 (start_datetime, truncated_start),
952 (end_datetime, truncated_end),
953 ],
954 )
955
956 def assertDateKind(kind):
957 truncated_start = truncate_to(start_datetime.date(), kind)
958 truncated_end = truncate_to(end_datetime.date(), kind)
959 queryset = DTModel.objects.annotate(
960 truncated=Trunc("start_date", kind, output_field=DateField())
961 ).order_by("start_datetime")
962 self.assertSequenceEqual(
963 queryset.values_list("start_datetime", "truncated"),
964 [
965 (start_datetime, truncated_start),
966 (end_datetime, truncated_end),
967 ],
968 )
969
970 def assertTimeKind(kind):
971 truncated_start = truncate_to(start_datetime.time(), kind)
972 truncated_end = truncate_to(end_datetime.time(), kind)
973 queryset = DTModel.objects.annotate(
974 truncated=Trunc("start_time", kind, output_field=TimeField())
975 ).order_by("start_datetime")
976 self.assertSequenceEqual(
977 queryset.values_list("start_datetime", "truncated"),
978 [
979 (start_datetime, truncated_start),
980 (end_datetime, truncated_end),
981 ],
982 )
983
984 def assertDatetimeToTimeKind(kind):
985 truncated_start = truncate_to(start_datetime.time(), kind)
986 truncated_end = truncate_to(end_datetime.time(), kind)
987 queryset = DTModel.objects.annotate(
988 truncated=Trunc("start_datetime", kind, output_field=TimeField()),
989 ).order_by("start_datetime")
990 self.assertSequenceEqual(
991 queryset.values_list("start_datetime", "truncated"),
992 [
993 (start_datetime, truncated_start),
994 (end_datetime, truncated_end),
995 ],
996 )
997
998 date_truncations = ["year", "quarter", "month", "day"]
999 time_truncations = ["hour", "minute", "second"]
1000 tests = [
1001 (assertDateKind, date_truncations),
1002 (assertTimeKind, time_truncations),
1003 (assertDatetimeKind, [*date_truncations, *time_truncations]),
1004 (assertDatetimeToTimeKind, time_truncations),
1005 ]
1006 for assertion, truncations in tests:
1007 for truncation in truncations:
1008 with self.subTest(assertion=assertion.__name__, truncation=truncation):
1009 assertion(truncation)
1010
1011 qs = DTModel.objects.filter(
1012 start_datetime__date=Trunc(
1013 "start_datetime", "day", output_field=DateField()
1014 )
1015 )
1016 self.assertEqual(qs.count(), 2)
1017
1018 def _test_trunc_week(self, start_datetime, end_datetime):
1019 if settings.USE_TZ:
1020 start_datetime = timezone.make_aware(start_datetime)
1021 end_datetime = timezone.make_aware(end_datetime)
1022 self.create_model(start_datetime, end_datetime)
1023 self.create_model(end_datetime, start_datetime)
1024
1025 self.assertQuerySetEqual(
1026 DTModel.objects.annotate(
1027 truncated=Trunc("start_datetime", "week", output_field=DateTimeField())
1028 ).order_by("start_datetime"),
1029 [
1030 (start_datetime, truncate_to(start_datetime, "week")),
1031 (end_datetime, truncate_to(end_datetime, "week")),
1032 ],
1033 lambda m: (m.start_datetime, m.truncated),
1034 )
1035 self.assertQuerySetEqual(
1036 DTModel.objects.annotate(
1037 truncated=Trunc("start_date", "week", output_field=DateField())
1038 ).order_by("start_datetime"),
1039 [
1040 (start_datetime, truncate_to(start_datetime.date(), "week")),
1041 (end_datetime, truncate_to(end_datetime.date(), "week")),
1042 ],
1043 lambda m: (m.start_datetime, m.truncated),
1044 )
1045
1046 def test_trunc_week(self):
1047 self._test_trunc_week(
1048 start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321),
1049 end_datetime=datetime(2016, 6, 15, 14, 10, 50, 123),
1050 )
1051
1052 def test_trunc_week_before_1000(self):
1053 self._test_trunc_week(
1054 start_datetime=datetime(999, 6, 15, 14, 30, 50, 321),
1055 end_datetime=datetime(2016, 6, 15, 14, 10, 50, 123),
1056 )
1057
1058 def test_trunc_invalid_arguments(self):
1059 msg = "output_field must be either DateField, TimeField, or DateTimeField"
1060 with self.assertRaisesMessage(ValueError, msg):
1061 list(
1062 DTModel.objects.annotate(
1063 truncated=Trunc(
1064 "start_datetime", "year", output_field=IntegerField()
1065 ),
1066 )
1067 )
1068 msg = "'name' isn't a DateField, TimeField, or DateTimeField."
1069 with self.assertRaisesMessage(TypeError, msg):
1070 list(
1071 DTModel.objects.annotate(
1072 truncated=Trunc("name", "year", output_field=DateTimeField()),
1073 )
1074 )
1075 msg = "Cannot truncate DateField 'start_date' to DateTimeField"
1076 with self.assertRaisesMessage(ValueError, msg):
1077 list(DTModel.objects.annotate(truncated=Trunc("start_date", "second")))
1078 with self.assertRaisesMessage(ValueError, msg):
1079 list(
1080 DTModel.objects.annotate(
1081 truncated=Trunc(
1082 "start_date", "month", output_field=DateTimeField()
1083 ),
1084 )
1085 )
1086 msg = "Cannot truncate TimeField 'start_time' to DateTimeField"
1087 with self.assertRaisesMessage(ValueError, msg):
1088 list(DTModel.objects.annotate(truncated=Trunc("start_time", "month")))
1089 with self.assertRaisesMessage(ValueError, msg):
1090 list(
1091 DTModel.objects.annotate(
1092 truncated=Trunc(
1093 "start_time", "second", output_field=DateTimeField()
1094 ),
1095 )
1096 )
1097
1098 def test_trunc_none(self):
1099 self.create_model(None, None)
1100 for t in (
1101 Trunc("start_datetime", "year"),
1102 Trunc("start_date", "year"),
1103 Trunc("start_time", "hour"),
1104 ):
1105 with self.subTest(t):
1106 self.assertIsNone(
1107 DTModel.objects.annotate(truncated=t).first().truncated
1108 )
1109
1110 def test_trunc_year_func(self):
1111 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1112 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "year")
1113 if settings.USE_TZ:
1114 start_datetime = timezone.make_aware(start_datetime)
1115 end_datetime = timezone.make_aware(end_datetime)
1116 self.create_model(start_datetime, end_datetime)
1117 self.create_model(end_datetime, start_datetime)
1118 self.assertQuerySetEqual(
1119 DTModel.objects.annotate(extracted=TruncYear("start_datetime")).order_by(
1120 "start_datetime"
1121 ),
1122 [
1123 (start_datetime, truncate_to(start_datetime, "year")),
1124 (end_datetime, truncate_to(end_datetime, "year")),
1125 ],
1126 lambda m: (m.start_datetime, m.extracted),
1127 )
1128 self.assertQuerySetEqual(
1129 DTModel.objects.annotate(extracted=TruncYear("start_date")).order_by(
1130 "start_datetime"
1131 ),
1132 [
1133 (start_datetime, truncate_to(start_datetime.date(), "year")),
1134 (end_datetime, truncate_to(end_datetime.date(), "year")),
1135 ],
1136 lambda m: (m.start_datetime, m.extracted),
1137 )
1138 self.assertEqual(
1139 DTModel.objects.filter(start_datetime=TruncYear("start_datetime")).count(),
1140 1,
1141 )
1142
1143 with self.assertRaisesMessage(
1144 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1145 ):
1146 list(DTModel.objects.annotate(truncated=TruncYear("start_time")))
1147
1148 with self.assertRaisesMessage(
1149 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1150 ):
1151 list(
1152 DTModel.objects.annotate(
1153 truncated=TruncYear("start_time", output_field=TimeField())
1154 )
1155 )
1156
1157 def test_trunc_quarter_func(self):
1158 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1159 end_datetime = truncate_to(datetime(2016, 10, 15, 14, 10, 50, 123), "quarter")
1160 last_quarter_2015 = truncate_to(
1161 datetime(2015, 12, 31, 14, 10, 50, 123), "quarter"
1162 )
1163 first_quarter_2016 = truncate_to(
1164 datetime(2016, 1, 1, 14, 10, 50, 123), "quarter"
1165 )
1166 if settings.USE_TZ:
1167 start_datetime = timezone.make_aware(start_datetime)
1168 end_datetime = timezone.make_aware(end_datetime)
1169 last_quarter_2015 = timezone.make_aware(last_quarter_2015)
1170 first_quarter_2016 = timezone.make_aware(first_quarter_2016)
1171 self.create_model(start_datetime=start_datetime, end_datetime=end_datetime)
1172 self.create_model(start_datetime=end_datetime, end_datetime=start_datetime)
1173 self.create_model(start_datetime=last_quarter_2015, end_datetime=end_datetime)
1174 self.create_model(start_datetime=first_quarter_2016, end_datetime=end_datetime)
1175 self.assertQuerySetEqual(
1176 DTModel.objects.annotate(extracted=TruncQuarter("start_date")).order_by(
1177 "start_datetime"
1178 ),
1179 [
1180 (start_datetime, truncate_to(start_datetime.date(), "quarter")),
1181 (last_quarter_2015, truncate_to(last_quarter_2015.date(), "quarter")),
1182 (first_quarter_2016, truncate_to(first_quarter_2016.date(), "quarter")),
1183 (end_datetime, truncate_to(end_datetime.date(), "quarter")),
1184 ],
1185 lambda m: (m.start_datetime, m.extracted),
1186 )
1187 self.assertQuerySetEqual(
1188 DTModel.objects.annotate(extracted=TruncQuarter("start_datetime")).order_by(
1189 "start_datetime"
1190 ),
1191 [
1192 (start_datetime, truncate_to(start_datetime, "quarter")),
1193 (last_quarter_2015, truncate_to(last_quarter_2015, "quarter")),
1194 (first_quarter_2016, truncate_to(first_quarter_2016, "quarter")),
1195 (end_datetime, truncate_to(end_datetime, "quarter")),
1196 ],
1197 lambda m: (m.start_datetime, m.extracted),
1198 )
1199
1200 with self.assertRaisesMessage(
1201 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1202 ):
1203 list(DTModel.objects.annotate(truncated=TruncQuarter("start_time")))
1204
1205 with self.assertRaisesMessage(
1206 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1207 ):
1208 list(
1209 DTModel.objects.annotate(
1210 truncated=TruncQuarter("start_time", output_field=TimeField())
1211 )
1212 )
1213
1214 def test_trunc_month_func(self):
1215 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1216 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "month")
1217 if settings.USE_TZ:
1218 start_datetime = timezone.make_aware(start_datetime)
1219 end_datetime = timezone.make_aware(end_datetime)
1220 self.create_model(start_datetime, end_datetime)
1221 self.create_model(end_datetime, start_datetime)
1222 self.assertQuerySetEqual(
1223 DTModel.objects.annotate(extracted=TruncMonth("start_datetime")).order_by(
1224 "start_datetime"
1225 ),
1226 [
1227 (start_datetime, truncate_to(start_datetime, "month")),
1228 (end_datetime, truncate_to(end_datetime, "month")),
1229 ],
1230 lambda m: (m.start_datetime, m.extracted),
1231 )
1232 self.assertQuerySetEqual(
1233 DTModel.objects.annotate(extracted=TruncMonth("start_date")).order_by(
1234 "start_datetime"
1235 ),
1236 [
1237 (start_datetime, truncate_to(start_datetime.date(), "month")),
1238 (end_datetime, truncate_to(end_datetime.date(), "month")),
1239 ],
1240 lambda m: (m.start_datetime, m.extracted),
1241 )
1242 self.assertEqual(
1243 DTModel.objects.filter(start_datetime=TruncMonth("start_datetime")).count(),
1244 1,
1245 )
1246
1247 with self.assertRaisesMessage(
1248 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1249 ):
1250 list(DTModel.objects.annotate(truncated=TruncMonth("start_time")))
1251
1252 with self.assertRaisesMessage(
1253 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1254 ):
1255 list(
1256 DTModel.objects.annotate(
1257 truncated=TruncMonth("start_time", output_field=TimeField())
1258 )
1259 )
1260
1261 def test_trunc_week_func(self):
1262 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1263 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "week")
1264 if settings.USE_TZ:
1265 start_datetime = timezone.make_aware(start_datetime)
1266 end_datetime = timezone.make_aware(end_datetime)
1267 self.create_model(start_datetime, end_datetime)
1268 self.create_model(end_datetime, start_datetime)
1269 self.assertQuerySetEqual(
1270 DTModel.objects.annotate(extracted=TruncWeek("start_datetime")).order_by(
1271 "start_datetime"
1272 ),
1273 [
1274 (start_datetime, truncate_to(start_datetime, "week")),
1275 (end_datetime, truncate_to(end_datetime, "week")),
1276 ],
1277 lambda m: (m.start_datetime, m.extracted),
1278 )
1279 self.assertEqual(
1280 DTModel.objects.filter(start_datetime=TruncWeek("start_datetime")).count(),
1281 1,
1282 )
1283
1284 with self.assertRaisesMessage(
1285 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1286 ):
1287 list(DTModel.objects.annotate(truncated=TruncWeek("start_time")))
1288
1289 with self.assertRaisesMessage(
1290 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1291 ):
1292 list(
1293 DTModel.objects.annotate(
1294 truncated=TruncWeek("start_time", output_field=TimeField())
1295 )
1296 )
1297
1298 def test_trunc_date_func(self):
1299 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1300 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
1301 if settings.USE_TZ:
1302 start_datetime = timezone.make_aware(start_datetime)
1303 end_datetime = timezone.make_aware(end_datetime)
1304 self.create_model(start_datetime, end_datetime)
1305 self.create_model(end_datetime, start_datetime)
1306 self.assertQuerySetEqual(
1307 DTModel.objects.annotate(extracted=TruncDate("start_datetime")).order_by(
1308 "start_datetime"
1309 ),
1310 [
1311 (start_datetime, start_datetime.date()),
1312 (end_datetime, end_datetime.date()),
1313 ],
1314 lambda m: (m.start_datetime, m.extracted),
1315 )
1316 self.assertEqual(
1317 DTModel.objects.filter(
1318 start_datetime__date=TruncDate("start_datetime")
1319 ).count(),
1320 2,
1321 )
1322
1323 with self.assertRaisesMessage(
1324 ValueError, "Cannot truncate TimeField 'start_time' to DateField"
1325 ):
1326 list(DTModel.objects.annotate(truncated=TruncDate("start_time")))
1327
1328 with self.assertRaisesMessage(
1329 ValueError, "Cannot truncate TimeField 'start_time' to DateField"
1330 ):
1331 list(
1332 DTModel.objects.annotate(
1333 truncated=TruncDate("start_time", output_field=TimeField())
1334 )
1335 )
1336
1337 def test_trunc_date_none(self):
1338 self.create_model(None, None)
1339 self.assertIsNone(
1340 DTModel.objects.annotate(truncated=TruncDate("start_datetime"))
1341 .first()
1342 .truncated
1343 )
1344
1345 def test_trunc_time_func(self):
1346 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1347 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
1348 if settings.USE_TZ:
1349 start_datetime = timezone.make_aware(start_datetime)
1350 end_datetime = timezone.make_aware(end_datetime)
1351 self.create_model(start_datetime, end_datetime)
1352 self.create_model(end_datetime, start_datetime)
1353 self.assertQuerySetEqual(
1354 DTModel.objects.annotate(extracted=TruncTime("start_datetime")).order_by(
1355 "start_datetime"
1356 ),
1357 [
1358 (start_datetime, start_datetime.time()),
1359 (end_datetime, end_datetime.time()),
1360 ],
1361 lambda m: (m.start_datetime, m.extracted),
1362 )
1363 self.assertEqual(
1364 DTModel.objects.filter(
1365 start_datetime__time=TruncTime("start_datetime")
1366 ).count(),
1367 2,
1368 )
1369
1370 with self.assertRaisesMessage(
1371 ValueError, "Cannot truncate DateField 'start_date' to TimeField"
1372 ):
1373 list(DTModel.objects.annotate(truncated=TruncTime("start_date")))
1374
1375 with self.assertRaisesMessage(
1376 ValueError, "Cannot truncate DateField 'start_date' to TimeField"
1377 ):
1378 list(
1379 DTModel.objects.annotate(
1380 truncated=TruncTime("start_date", output_field=DateField())
1381 )
1382 )
1383
1384 def test_trunc_time_none(self):
1385 self.create_model(None, None)
1386 self.assertIsNone(
1387 DTModel.objects.annotate(truncated=TruncTime("start_datetime"))
1388 .first()
1389 .truncated
1390 )
1391
1392 def test_trunc_time_comparison(self):
1393 start_datetime = datetime(2015, 6, 15, 14, 30, 26) # 0 microseconds.
1394 end_datetime = datetime(2015, 6, 15, 14, 30, 26, 321)
1395 if settings.USE_TZ:
1396 start_datetime = timezone.make_aware(start_datetime)
1397 end_datetime = timezone.make_aware(end_datetime)
1398 self.create_model(start_datetime, end_datetime)
1399 self.assertIs(
1400 DTModel.objects.filter(
1401 start_datetime__time=start_datetime.time(),
1402 end_datetime__time=end_datetime.time(),
1403 ).exists(),
1404 True,
1405 )
1406 self.assertIs(
1407 DTModel.objects.annotate(
1408 extracted_start=TruncTime("start_datetime"),
1409 extracted_end=TruncTime("end_datetime"),
1410 )
1411 .filter(
1412 extracted_start=start_datetime.time(),
1413 extracted_end=end_datetime.time(),
1414 )
1415 .exists(),
1416 True,
1417 )
1418
1419 def test_trunc_day_func(self):
1420 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1421 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "day")
1422 if settings.USE_TZ:
1423 start_datetime = timezone.make_aware(start_datetime)
1424 end_datetime = timezone.make_aware(end_datetime)
1425 self.create_model(start_datetime, end_datetime)
1426 self.create_model(end_datetime, start_datetime)
1427 self.assertQuerySetEqual(
1428 DTModel.objects.annotate(extracted=TruncDay("start_datetime")).order_by(
1429 "start_datetime"
1430 ),
1431 [
1432 (start_datetime, truncate_to(start_datetime, "day")),
1433 (end_datetime, truncate_to(end_datetime, "day")),
1434 ],
1435 lambda m: (m.start_datetime, m.extracted),
1436 )
1437 self.assertEqual(
1438 DTModel.objects.filter(start_datetime=TruncDay("start_datetime")).count(), 1
1439 )
1440
1441 with self.assertRaisesMessage(
1442 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1443 ):
1444 list(DTModel.objects.annotate(truncated=TruncDay("start_time")))
1445
1446 with self.assertRaisesMessage(
1447 ValueError, "Cannot truncate TimeField 'start_time' to DateTimeField"
1448 ):
1449 list(
1450 DTModel.objects.annotate(
1451 truncated=TruncDay("start_time", output_field=TimeField())
1452 )
1453 )
1454
1455 def test_trunc_hour_func(self):
1456 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1457 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "hour")
1458 if settings.USE_TZ:
1459 start_datetime = timezone.make_aware(start_datetime)
1460 end_datetime = timezone.make_aware(end_datetime)
1461 self.create_model(start_datetime, end_datetime)
1462 self.create_model(end_datetime, start_datetime)
1463 self.assertQuerySetEqual(
1464 DTModel.objects.annotate(extracted=TruncHour("start_datetime")).order_by(
1465 "start_datetime"
1466 ),
1467 [
1468 (start_datetime, truncate_to(start_datetime, "hour")),
1469 (end_datetime, truncate_to(end_datetime, "hour")),
1470 ],
1471 lambda m: (m.start_datetime, m.extracted),
1472 )
1473 self.assertQuerySetEqual(
1474 DTModel.objects.annotate(extracted=TruncHour("start_time")).order_by(
1475 "start_datetime"
1476 ),
1477 [
1478 (start_datetime, truncate_to(start_datetime.time(), "hour")),
1479 (end_datetime, truncate_to(end_datetime.time(), "hour")),
1480 ],
1481 lambda m: (m.start_datetime, m.extracted),
1482 )
1483 self.assertEqual(
1484 DTModel.objects.filter(start_datetime=TruncHour("start_datetime")).count(),
1485 1,
1486 )
1487
1488 with self.assertRaisesMessage(
1489 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1490 ):
1491 list(DTModel.objects.annotate(truncated=TruncHour("start_date")))
1492
1493 with self.assertRaisesMessage(
1494 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1495 ):
1496 list(
1497 DTModel.objects.annotate(
1498 truncated=TruncHour("start_date", output_field=DateField())
1499 )
1500 )
1501
1502 def test_trunc_minute_func(self):
1503 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1504 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "minute")
1505 if settings.USE_TZ:
1506 start_datetime = timezone.make_aware(start_datetime)
1507 end_datetime = timezone.make_aware(end_datetime)
1508 self.create_model(start_datetime, end_datetime)
1509 self.create_model(end_datetime, start_datetime)
1510 self.assertQuerySetEqual(
1511 DTModel.objects.annotate(extracted=TruncMinute("start_datetime")).order_by(
1512 "start_datetime"
1513 ),
1514 [
1515 (start_datetime, truncate_to(start_datetime, "minute")),
1516 (end_datetime, truncate_to(end_datetime, "minute")),
1517 ],
1518 lambda m: (m.start_datetime, m.extracted),
1519 )
1520 self.assertQuerySetEqual(
1521 DTModel.objects.annotate(extracted=TruncMinute("start_time")).order_by(
1522 "start_datetime"
1523 ),
1524 [
1525 (start_datetime, truncate_to(start_datetime.time(), "minute")),
1526 (end_datetime, truncate_to(end_datetime.time(), "minute")),
1527 ],
1528 lambda m: (m.start_datetime, m.extracted),
1529 )
1530 self.assertEqual(
1531 DTModel.objects.filter(
1532 start_datetime=TruncMinute("start_datetime")
1533 ).count(),
1534 1,
1535 )
1536
1537 with self.assertRaisesMessage(
1538 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1539 ):
1540 list(DTModel.objects.annotate(truncated=TruncMinute("start_date")))
1541
1542 with self.assertRaisesMessage(
1543 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1544 ):
1545 list(
1546 DTModel.objects.annotate(
1547 truncated=TruncMinute("start_date", output_field=DateField())
1548 )
1549 )
1550
1551 def test_trunc_second_func(self):
1552 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1553 end_datetime = truncate_to(datetime(2016, 6, 15, 14, 10, 50, 123), "second")
1554 if settings.USE_TZ:
1555 start_datetime = timezone.make_aware(start_datetime)
1556 end_datetime = timezone.make_aware(end_datetime)
1557 self.create_model(start_datetime, end_datetime)
1558 self.create_model(end_datetime, start_datetime)
1559 self.assertQuerySetEqual(
1560 DTModel.objects.annotate(extracted=TruncSecond("start_datetime")).order_by(
1561 "start_datetime"
1562 ),
1563 [
1564 (start_datetime, truncate_to(start_datetime, "second")),
1565 (end_datetime, truncate_to(end_datetime, "second")),
1566 ],
1567 lambda m: (m.start_datetime, m.extracted),
1568 )
1569 self.assertQuerySetEqual(
1570 DTModel.objects.annotate(extracted=TruncSecond("start_time")).order_by(
1571 "start_datetime"
1572 ),
1573 [
1574 (start_datetime, truncate_to(start_datetime.time(), "second")),
1575 (end_datetime, truncate_to(end_datetime.time(), "second")),
1576 ],
1577 lambda m: (m.start_datetime, m.extracted),
1578 )
1579 self.assertEqual(
1580 DTModel.objects.filter(
1581 start_datetime=TruncSecond("start_datetime")
1582 ).count(),
1583 1,
1584 )
1585
1586 with self.assertRaisesMessage(
1587 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1588 ):
1589 list(DTModel.objects.annotate(truncated=TruncSecond("start_date")))
1590
1591 with self.assertRaisesMessage(
1592 ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"
1593 ):
1594 list(
1595 DTModel.objects.annotate(
1596 truncated=TruncSecond("start_date", output_field=DateField())
1597 )
1598 )
1599
1600 def test_trunc_subquery_with_parameters(self):
1601 author_1 = Author.objects.create(name="J. R. R. Tolkien")
1602 author_2 = Author.objects.create(name="G. R. R. Martin")
1603 fan_since_1 = datetime(2016, 2, 3, 15, 0, 0)
1604 fan_since_2 = datetime(2015, 2, 3, 15, 0, 0)
1605 fan_since_3 = datetime(2017, 2, 3, 15, 0, 0)
1606 if settings.USE_TZ:
1607 fan_since_1 = timezone.make_aware(fan_since_1)
1608 fan_since_2 = timezone.make_aware(fan_since_2)
1609 fan_since_3 = timezone.make_aware(fan_since_3)
1610 Fan.objects.create(author=author_1, name="Tom", fan_since=fan_since_1)
1611 Fan.objects.create(author=author_1, name="Emma", fan_since=fan_since_2)
1612 Fan.objects.create(author=author_2, name="Isabella", fan_since=fan_since_3)
1613
1614 inner = (
1615 Fan.objects.filter(
1616 author=OuterRef("pk"), name__in=("Emma", "Isabella", "Tom")
1617 )
1618 .values("author")
1619 .annotate(newest_fan=Max("fan_since"))
1620 .values("newest_fan")
1621 )
1622 outer = Author.objects.annotate(
1623 newest_fan_year=TruncYear(Subquery(inner, output_field=DateTimeField()))
1624 )
1625 tz = datetime_timezone.utc if settings.USE_TZ else None
1626 self.assertSequenceEqual(
1627 outer.order_by("name").values("name", "newest_fan_year"),
1628 [
1629 {
1630 "name": "G. R. R. Martin",
1631 "newest_fan_year": datetime(2017, 1, 1, 0, 0, tzinfo=tz),
1632 },
1633 {
1634 "name": "J. R. R. Tolkien",
1635 "newest_fan_year": datetime(2016, 1, 1, 0, 0, tzinfo=tz),
1636 },
1637 ],
1638 )
1639
1640 def test_extract_outerref(self):
1641 datetime_1 = datetime(2000, 1, 1)
1642 datetime_2 = datetime(2001, 3, 5)
1643 datetime_3 = datetime(2002, 1, 3)
1644 if settings.USE_TZ:
1645 datetime_1 = timezone.make_aware(datetime_1)
1646 datetime_2 = timezone.make_aware(datetime_2)
1647 datetime_3 = timezone.make_aware(datetime_3)
1648 obj_1 = self.create_model(datetime_1, datetime_3)
1649 obj_2 = self.create_model(datetime_2, datetime_1)
1650 obj_3 = self.create_model(datetime_3, datetime_2)
1651
1652 inner_qs = DTModel.objects.filter(
1653 start_datetime__year=2000,
1654 start_datetime__month=ExtractMonth(OuterRef("end_datetime")),
1655 )
1656 qs = DTModel.objects.annotate(
1657 related_pk=Subquery(inner_qs.values("pk")[:1]),
1658 )
1659 self.assertSequenceEqual(
1660 qs.order_by("name").values("pk", "related_pk"),
1661 [
1662 {"pk": obj_1.pk, "related_pk": obj_1.pk},
1663 {"pk": obj_2.pk, "related_pk": obj_1.pk},
1664 {"pk": obj_3.pk, "related_pk": None},
1665 ],
1666 )
1667
1668
1669 @override_settings(USE_TZ=True, TIME_ZONE="UTC")
1670 class DateFunctionWithTimeZoneTests(DateFunctionTests):
1671 def test_extract_func_with_timezone(self):
1672 start_datetime = datetime(2015, 6, 15, 23, 30, 1, 321)
1673 end_datetime = datetime(2015, 6, 16, 13, 11, 27, 123)
1674 start_datetime = timezone.make_aware(start_datetime)
1675 end_datetime = timezone.make_aware(end_datetime)
1676 self.create_model(start_datetime, end_datetime)
1677 delta_tzinfo_pos = datetime_timezone(timedelta(hours=5))
1678 delta_tzinfo_neg = datetime_timezone(timedelta(hours=-5, minutes=17))
1679 melb = zoneinfo.ZoneInfo("Australia/Melbourne")
1680
1681 qs = DTModel.objects.annotate(
1682 day=Extract("start_datetime", "day"),
1683 day_melb=Extract("start_datetime", "day", tzinfo=melb),
1684 week=Extract("start_datetime", "week", tzinfo=melb),
1685 isoyear=ExtractIsoYear("start_datetime", tzinfo=melb),
1686 weekday=ExtractWeekDay("start_datetime"),
1687 weekday_melb=ExtractWeekDay("start_datetime", tzinfo=melb),
1688 isoweekday=ExtractIsoWeekDay("start_datetime"),
1689 isoweekday_melb=ExtractIsoWeekDay("start_datetime", tzinfo=melb),
1690 quarter=ExtractQuarter("start_datetime", tzinfo=melb),
1691 hour=ExtractHour("start_datetime"),
1692 hour_melb=ExtractHour("start_datetime", tzinfo=melb),
1693 hour_with_delta_pos=ExtractHour("start_datetime", tzinfo=delta_tzinfo_pos),
1694 hour_with_delta_neg=ExtractHour("start_datetime", tzinfo=delta_tzinfo_neg),
1695 minute_with_delta_neg=ExtractMinute(
1696 "start_datetime", tzinfo=delta_tzinfo_neg
1697 ),
1698 ).order_by("start_datetime")
1699
1700 utc_model = qs.get()
1701 self.assertEqual(utc_model.day, 15)
1702 self.assertEqual(utc_model.day_melb, 16)
1703 self.assertEqual(utc_model.week, 25)
1704 self.assertEqual(utc_model.isoyear, 2015)
1705 self.assertEqual(utc_model.weekday, 2)
1706 self.assertEqual(utc_model.weekday_melb, 3)
1707 self.assertEqual(utc_model.isoweekday, 1)
1708 self.assertEqual(utc_model.isoweekday_melb, 2)
1709 self.assertEqual(utc_model.quarter, 2)
1710 self.assertEqual(utc_model.hour, 23)
1711 self.assertEqual(utc_model.hour_melb, 9)
1712 self.assertEqual(utc_model.hour_with_delta_pos, 4)
1713 self.assertEqual(utc_model.hour_with_delta_neg, 18)
1714 self.assertEqual(utc_model.minute_with_delta_neg, 47)
1715
1716 with timezone.override(melb):
1717 melb_model = qs.get()
1718
1719 self.assertEqual(melb_model.day, 16)
1720 self.assertEqual(melb_model.day_melb, 16)
1721 self.assertEqual(melb_model.week, 25)
1722 self.assertEqual(melb_model.isoyear, 2015)
1723 self.assertEqual(melb_model.weekday, 3)
1724 self.assertEqual(melb_model.isoweekday, 2)
1725 self.assertEqual(melb_model.quarter, 2)
1726 self.assertEqual(melb_model.weekday_melb, 3)
1727 self.assertEqual(melb_model.isoweekday_melb, 2)
1728 self.assertEqual(melb_model.hour, 9)
1729 self.assertEqual(melb_model.hour_melb, 9)
1730
1731 def test_extract_func_with_timezone_minus_no_offset(self):
1732 start_datetime = datetime(2015, 6, 15, 23, 30, 1, 321)
1733 end_datetime = datetime(2015, 6, 16, 13, 11, 27, 123)
1734 start_datetime = timezone.make_aware(start_datetime)
1735 end_datetime = timezone.make_aware(end_datetime)
1736 self.create_model(start_datetime, end_datetime)
1737 ust_nera = zoneinfo.ZoneInfo("Asia/Ust-Nera")
1738
1739 qs = DTModel.objects.annotate(
1740 hour=ExtractHour("start_datetime"),
1741 hour_tz=ExtractHour("start_datetime", tzinfo=ust_nera),
1742 ).order_by("start_datetime")
1743
1744 utc_model = qs.get()
1745 self.assertEqual(utc_model.hour, 23)
1746 self.assertEqual(utc_model.hour_tz, 9)
1747
1748 with timezone.override(ust_nera):
1749 ust_nera_model = qs.get()
1750
1751 self.assertEqual(ust_nera_model.hour, 9)
1752 self.assertEqual(ust_nera_model.hour_tz, 9)
1753
1754 def test_extract_func_explicit_timezone_priority(self):
1755 start_datetime = datetime(2015, 6, 15, 23, 30, 1, 321)
1756 end_datetime = datetime(2015, 6, 16, 13, 11, 27, 123)
1757 start_datetime = timezone.make_aware(start_datetime)
1758 end_datetime = timezone.make_aware(end_datetime)
1759 self.create_model(start_datetime, end_datetime)
1760 melb = zoneinfo.ZoneInfo("Australia/Melbourne")
1761 with timezone.override(melb):
1762 model = (
1763 DTModel.objects.annotate(
1764 day_melb=Extract("start_datetime", "day"),
1765 day_utc=Extract(
1766 "start_datetime", "day", tzinfo=datetime_timezone.utc
1767 ),
1768 )
1769 .order_by("start_datetime")
1770 .get()
1771 )
1772 self.assertEqual(model.day_melb, 16)
1773 self.assertEqual(model.day_utc, 15)
1774
1775 def test_extract_invalid_field_with_timezone(self):
1776 melb = zoneinfo.ZoneInfo("Australia/Melbourne")
1777 msg = "tzinfo can only be used with DateTimeField."
1778 with self.assertRaisesMessage(ValueError, msg):
1779 DTModel.objects.annotate(
1780 day_melb=Extract("start_date", "day", tzinfo=melb),
1781 ).get()
1782 with self.assertRaisesMessage(ValueError, msg):
1783 DTModel.objects.annotate(
1784 hour_melb=Extract("start_time", "hour", tzinfo=melb),
1785 ).get()
1786
1787 def test_trunc_timezone_applied_before_truncation(self):
1788 start_datetime = datetime(2016, 1, 1, 1, 30, 50, 321)
1789 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
1790 start_datetime = timezone.make_aware(start_datetime)
1791 end_datetime = timezone.make_aware(end_datetime)
1792 self.create_model(start_datetime, end_datetime)
1793 melb = zoneinfo.ZoneInfo("Australia/Melbourne")
1794 pacific = zoneinfo.ZoneInfo("America/Los_Angeles")
1795
1796 model = (
1797 DTModel.objects.annotate(
1798 melb_year=TruncYear("start_datetime", tzinfo=melb),
1799 pacific_year=TruncYear("start_datetime", tzinfo=pacific),
1800 melb_date=TruncDate("start_datetime", tzinfo=melb),
1801 pacific_date=TruncDate("start_datetime", tzinfo=pacific),
1802 melb_time=TruncTime("start_datetime", tzinfo=melb),
1803 pacific_time=TruncTime("start_datetime", tzinfo=pacific),
1804 )
1805 .order_by("start_datetime")
1806 .get()
1807 )
1808
1809 melb_start_datetime = start_datetime.astimezone(melb)
1810 pacific_start_datetime = start_datetime.astimezone(pacific)
1811 self.assertEqual(model.start_datetime, start_datetime)
1812 self.assertEqual(model.melb_year, truncate_to(start_datetime, "year", melb))
1813 self.assertEqual(
1814 model.pacific_year, truncate_to(start_datetime, "year", pacific)
1815 )
1816 self.assertEqual(model.start_datetime.year, 2016)
1817 self.assertEqual(model.melb_year.year, 2016)
1818 self.assertEqual(model.pacific_year.year, 2015)
1819 self.assertEqual(model.melb_date, melb_start_datetime.date())
1820 self.assertEqual(model.pacific_date, pacific_start_datetime.date())
1821 self.assertEqual(model.melb_time, melb_start_datetime.time())
1822 self.assertEqual(model.pacific_time, pacific_start_datetime.time())
1823
1824 def test_trunc_func_with_timezone(self):
1825 """
1826 If the truncated datetime transitions to a different offset (daylight
1827 saving) then the returned value will have that new timezone/offset.
1828 """
1829 start_datetime = datetime(2015, 6, 15, 14, 30, 50, 321)
1830 end_datetime = datetime(2016, 6, 15, 14, 10, 50, 123)
1831 start_datetime = timezone.make_aware(start_datetime)
1832 end_datetime = timezone.make_aware(end_datetime)
1833 self.create_model(start_datetime, end_datetime)
1834 self.create_model(end_datetime, start_datetime)
1835
1836 def assertDatetimeKind(kind, tzinfo):
1837 truncated_start = truncate_to(
1838 start_datetime.astimezone(tzinfo), kind, tzinfo
1839 )
1840 truncated_end = truncate_to(end_datetime.astimezone(tzinfo), kind, tzinfo)
1841 queryset = DTModel.objects.annotate(
1842 truncated=Trunc(
1843 "start_datetime",
1844 kind,
1845 output_field=DateTimeField(),
1846 tzinfo=tzinfo,
1847 )
1848 ).order_by("start_datetime")
1849 self.assertSequenceEqual(
1850 queryset.values_list("start_datetime", "truncated"),
1851 [
1852 (start_datetime, truncated_start),
1853 (end_datetime, truncated_end),
1854 ],
1855 )
1856
1857 def assertDatetimeToDateKind(kind, tzinfo):
1858 truncated_start = truncate_to(
1859 start_datetime.astimezone(tzinfo).date(), kind
1860 )
1861 truncated_end = truncate_to(end_datetime.astimezone(tzinfo).date(), kind)
1862 queryset = DTModel.objects.annotate(
1863 truncated=Trunc(
1864 "start_datetime",
1865 kind,
1866 output_field=DateField(),
1867 tzinfo=tzinfo,
1868 ),
1869 ).order_by("start_datetime")
1870 self.assertSequenceEqual(
1871 queryset.values_list("start_datetime", "truncated"),
1872 [
1873 (start_datetime, truncated_start),
1874 (end_datetime, truncated_end),
1875 ],
1876 )
1877
1878 def assertDatetimeToTimeKind(kind, tzinfo):
1879 truncated_start = truncate_to(
1880 start_datetime.astimezone(tzinfo).time(), kind
1881 )
1882 truncated_end = truncate_to(end_datetime.astimezone(tzinfo).time(), kind)
1883 queryset = DTModel.objects.annotate(
1884 truncated=Trunc(
1885 "start_datetime",
1886 kind,
1887 output_field=TimeField(),
1888 tzinfo=tzinfo,
1889 )
1890 ).order_by("start_datetime")
1891 self.assertSequenceEqual(
1892 queryset.values_list("start_datetime", "truncated"),
1893 [
1894 (start_datetime, truncated_start),
1895 (end_datetime, truncated_end),
1896 ],
1897 )
1898
1899 timezones = [
1900 zoneinfo.ZoneInfo("Australia/Melbourne"),
1901 zoneinfo.ZoneInfo("Etc/GMT+10"),
1902 ]
1903 date_truncations = ["year", "quarter", "month", "week", "day"]
1904 time_truncations = ["hour", "minute", "second"]
1905 tests = [
1906 (assertDatetimeToDateKind, date_truncations),
1907 (assertDatetimeToTimeKind, time_truncations),
1908 (assertDatetimeKind, [*date_truncations, *time_truncations]),
1909 ]
1910 for assertion, truncations in tests:
1911 for truncation in truncations:
1912 for tzinfo in timezones:
1913 with self.subTest(
1914 assertion=assertion.__name__,
1915 truncation=truncation,
1916 tzinfo=tzinfo.key,
1917 ):
1918 assertion(truncation, tzinfo)
1919
1920 qs = DTModel.objects.filter(
1921 start_datetime__date=Trunc(
1922 "start_datetime", "day", output_field=DateField()
1923 )
1924 )
1925 self.assertEqual(qs.count(), 2)
1926
1927 def test_trunc_invalid_field_with_timezone(self):
1928 melb = zoneinfo.ZoneInfo("Australia/Melbourne")
1929 msg = "tzinfo can only be used with DateTimeField."
1930 with self.assertRaisesMessage(ValueError, msg):
1931 DTModel.objects.annotate(
1932 day_melb=Trunc("start_date", "day", tzinfo=melb),
1933 ).get()
1934 with self.assertRaisesMessage(ValueError, msg):
1935 DTModel.objects.annotate(
1936 hour_melb=Trunc("start_time", "hour", tzinfo=melb),
1937 ).get()