Fixed #34070 -- Added subsecond support to Now() on SQLite and MySQL. #16111

LilyFirefly · 2022-09-25T12:33:08Z

Extracted from #16092 as suggested in #16092 (comment).

felixxm · 2022-09-26T11:00:55Z

@LilyFoote Thanks 👍 Should we update also UTC_TIMESTAMP used in tests to the UTC_TIMESTAMP(6)?

django/django/db/backends/mysql/features.py

Line 84 in 3fb9c74

test_now_utc_template = "UTC_TIMESTAMP"

Can we add a regression test? For example by checking microseconds in NowTests.

LilyFirefly · 2022-09-30T20:30:28Z

Should we update also UTC_TIMESTAMP used in tests to the UTC_TIMESTAMP(6)?

I don't think it particularly matters - it's only used in the NowUTC subclass of Now in tests.aggregation.tests. Especially since NowUTC is only used in three tests and they always truncate to the hour or the date.

Thinking about it a bit more, it's weird to me that this is implemented as a feature at all, since it's purely for this one test class.

LilyFirefly · 2022-09-30T20:43:04Z

Can we add a regression test?

This is a bit tricky - the natural test to write checks that the microsecond attribute of the datetime object is greater than 0, but that's technically flaky, since a random datetime could happen to have microsecond set to 0.

My other idea is to make an assertion before we convert the database value to a python datetime object, but I'm not sure how to hook into that location in a test.

adamchainz · 2022-09-30T22:11:01Z

This is a bit tricky - the natural test to write checks that the microsecond attribute of the datetime object is greater than 0, but that's technically flaky, since a random datetime could happen to have microsecond set to 0.

1 in a million flaky is almost worse than 1 in 2 flaky 😅

Perhaps you can cast the result to a string and check it has 6 trailing digits of precision?

now_string = Something.objects.annotate(
    now_string=Cast(Now(), TextField())
).get().now_string
self.assertRegex(now_string, "^.*\.\d{6}")

You'd need to check that MySQL doesn't do like Python and not output the digits if it's 0 microseconds... Plus the test may need to be MySQL specific in case other DB's have different string formats.

LilyFirefly · 2022-09-30T22:16:07Z

Plus the test may need to be MySQL specific in case other DB's have different string formats.

It turns out sqlite doesn't do microsecond precision. There's a way to get millisecond precision: (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')), but I'm not sure if we want that here.

LilyFirefly · 2022-10-01T12:09:25Z

Thinking about it a bit more, I think we do want millisecond precision on sqlite, since it's better than the status quo.

LilyFirefly · 2022-10-01T13:14:53Z

buildbot, test on oracle.

adamchainz · 2022-10-01T13:50:30Z

Thinking about it a bit more, I think we do want millisecond precision on sqlite, since it's better than the status quo.

👍👍👍

tests/db_functions/datetime/test_now.py

docs/releases/4.2.txt

tests/db_functions/datetime/test_now.py

felixxm

@LilyFoote Thanks 👍

django/db/models/functions/datetime.py

LilyFirefly · 2022-10-03T21:41:09Z

django/db/models/functions/datetime.py

+        return self.as_sql(
+            compiler,
+            connection,
+            template="STRFTIME('%%Y-%%m-%%d %%H:%%M:%%f', 'NOW')",


@felixxm These needed to be %%%%: https://docs.djangoproject.com/en/4.1/ref/models/expressions/#django.db.models.Func.template

I'm looking into writing a standalone test that proves this, but it is currently causing the sqlite tests to break on #16092.

Actually, maybe this is actually a bug on the db defaults side...

I missed that it's documented, however both versions works on SQLite as it has a custom SQLiteCursorWrapper:

django/django/db/backends/sqlite3/base.py

Lines 355 to 376 in 649b28e

FORMAT_QMARK_REGEX = _lazy_re_compile(r"(?<!%)%s")

class SQLiteCursorWrapper(Database.Cursor):

"""

Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.

This fixes it -- but note that if you want to use a literal "%s" in a query,

you'll need to use "%%s".

"""

def execute(self, query, params=None):

if params is None:

return Database.Cursor.execute(self, query)

query = self.convert_query(query)

return Database.Cursor.execute(self, query, params)

def executemany(self, query, param_list):

query = self.convert_query(query)

return Database.Cursor.executemany(self, query, param_list)

def convert_query(self, query):

return FORMAT_QMARK_REGEX.sub("?", query).replace("%%", "%")

and the format string doesn't contain %s. I'm not sure if it's worth changing 🤔

LilyFirefly mentioned this pull request Sep 25, 2022

Fixed #470 -- Added support for database defaults on fields. #16092

Merged

adamchainz approved these changes Oct 1, 2022

View reviewed changes

tests/db_functions/datetime/test_now.py Outdated Show resolved Hide resolved

docs/releases/4.2.txt Outdated Show resolved Hide resolved

felixxm reviewed Oct 1, 2022

View reviewed changes

tests/db_functions/datetime/test_now.py Outdated Show resolved Hide resolved

adamchainz reviewed Oct 1, 2022

View reviewed changes

tests/db_functions/datetime/test_now.py Outdated Show resolved Hide resolved

felixxm changed the title ~~Support microsecond precision in Now on MySQL~~ Fixed #34070 -- Added microseconds support to Now() on SQLite and MySQL. Oct 3, 2022

felixxm changed the title ~~Fixed #34070 -- Added microseconds support to Now() on SQLite and MySQL.~~ Fixed #34070 -- Added subsecond support to Now() on SQLite and MySQL. Oct 3, 2022

felixxm reviewed Oct 3, 2022

View reviewed changes

django/db/models/functions/datetime.py Outdated Show resolved Hide resolved

felixxm force-pushed the now-microseconds-mysql branch 2 times, most recently from 0489f87 to d37c5bd Compare October 3, 2022 09:50

adamchainz approved these changes Oct 3, 2022

View reviewed changes

Fixed #34070 -- Added subsecond support to Now() on SQLite and MySQL.

649b28e

felixxm force-pushed the now-microseconds-mysql branch from d37c5bd to 649b28e Compare October 3, 2022 10:13

felixxm merged commit 649b28e into django:main Oct 3, 2022

LilyFirefly deleted the now-microseconds-mysql branch October 3, 2022 18:00

LilyFirefly commented Oct 3, 2022

View reviewed changes

felixxm temporarily deployed to schedules October 4, 2022 03:19 Inactive

felixxm mentioned this pull request Oct 24, 2022

Refs #34070 -- Fixed date format in Now() on SQLite. #16223

Merged

	FORMAT_QMARK_REGEX = _lazy_re_compile(r"(?<!%)%s")


	class SQLiteCursorWrapper(Database.Cursor):
	"""
	Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.
	This fixes it -- but note that if you want to use a literal "%s" in a query,
	you'll need to use "%%s".
	"""

	def execute(self, query, params=None):
	if params is None:
	return Database.Cursor.execute(self, query)
	query = self.convert_query(query)
	return Database.Cursor.execute(self, query, params)

	def executemany(self, query, param_list):
	query = self.convert_query(query)
	return Database.Cursor.executemany(self, query, param_list)

	def convert_query(self, query):
	return FORMAT_QMARK_REGEX.sub("?", query).replace("%%", "%")

Uh oh!

Fixed #34070 -- Added subsecond support to Now() on SQLite and MySQL. #16111

Fixed #34070 -- Added subsecond support to Now() on SQLite and MySQL. #16111

Uh oh!

Conversation

LilyFirefly commented Sep 25, 2022

Uh oh!

felixxm commented Sep 26, 2022

Uh oh!

LilyFirefly commented Sep 30, 2022

Uh oh!

LilyFirefly commented Sep 30, 2022

Uh oh!

adamchainz commented Sep 30, 2022

Uh oh!

LilyFirefly commented Sep 30, 2022

Uh oh!

LilyFirefly commented Oct 1, 2022

Uh oh!

LilyFirefly commented Oct 1, 2022

Uh oh!

adamchainz commented Oct 1, 2022

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

felixxm left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

LilyFirefly Oct 3, 2022

Choose a reason for hiding this comment

Uh oh!

LilyFirefly Oct 3, 2022

Choose a reason for hiding this comment

Uh oh!

felixxm Oct 4, 2022

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants