Re: [Question] MySQL Microseconds stripping

[Adam Johnson]

I've released the forementioned command as part of Django-MySQL, see here: 
https://django-mysql.readthedocs.org/en/latest/management_commands/fix_datetime_columns.html

On Friday, February 5, 2016 at 1:03:42 PM UTC, Adam Johnson wrote:
>
> Hi,
>
> I've just finished converting all the datetime columns to datetime(6) in 
> our database after an upgrade to MySQL 5.6. We don't use Django migrations, 
> many of these were done manually with *pt-online-schema-change*. Just 
> catching up on this thread, throwing some ideas in.
>
> Why not strip the microseconds explicitly as soon as you're handed a datetime 
>> with microseconds? 
>
>
> A note here: stripping isn't what MySQL does - it actually *rounds*. This 
> is the most annoying thing since it breaks a 5.6 replica feeding off a 5.5 
> master - it's reported as unfixed bug here: 
> https://bugs.mysql.com/bug.php?id=76948 . (Note: MariaDB kept the 
> stripping behaviour).
>
> We actually ended up patchy.patch ing 
> MySQLdb.times.DateTime2literal so that microseconds never escaped the 
> Python layer in the whole application, whilst we upgraded:
>
> from MySQLdb.times import DateTime2literal
> patchy.patch(DateTime2literal, """\
> @@ -1,3 +1,4 @@
>  def DateTime2literal(d, c):
>  \"""Format a DateTime object as an ISO timestamp.\"""
> +d = d.replace(microsecond=0)
>  return string_literal(format_TIMESTAMP(d), c)
> """)
>
> converting every datetime column of every table to datetime(6) and afford 
>> the extra storage (and probably
>
>
> There's no extra storage usage in fact - the old format of datetime 
> columns in 5.5 was less efficient than the new one in 5.6. Also, whenever 
> you migrate a table that was created on 5.5 with datetime columns on 5.6, 
> they get converted to the new format automatically. 
>
> Does Django have visibility of the field constraints at insert/select 
>> queryset time? 
>>
>
> Django could lookup the precision of datetime columns from 
> *information_schema.columns* , but I don't think it's necessarily a great 
> idea to change the field behaviour to match this.
>
> ./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?
>>
>
> If you're imagining a command to generate migrations, you can't do this 
> for third party apps. It might be better to have a command that simply 
> outputs, or runs, the SQL to alter all tables, outside of the migrations 
> framework? I'd've liked that, I ended up using a little script like that 
> here to generate the SQL and then run it manually/with 
> *pt-online-schema-change*. I'll look at open sourcing it in Django-MySQL 
> so you guys can look at it.
>
> On Tuesday, December 22, 2015 at 2:56:41 AM UTC, Cristiano Coelho wrote:
>>
>> I think a simple setting allowing to use the old behaviour should be 
>> enough, shouldn't it? How does it handle other db backends? I'm not sure if 
>> oracle has an option for datetime precision, but if it does, it makes sense 
>> to have a global setting for datetime precision, as right now you are 
>> pretty much forced to always go with a precision of 6 (at least on mysql?) 
>> and that might be just too much if you want a simpler datetime.
>>
>> El lunes, 21 de diciembre de 2015, 19:54:29 (UTC-3), Josh Smeaton 
>> escribió:
>>>
>>> I think this is a fairly big oversight that should be fixed in the most 
>>> backwards compatible way, so users don't need to change their code, or only 
>>> have to change it minimally. I'm with Aymeric here. Does Django have 
>>> visibility of the field constraints at insert/select queryset time? Ideally 
>>> Django would handle the differences transparently. If that's not possible 
>>> then we should have a migration or script that'll do the conversion on 
>>> behalf of users once off.
>>>
>>> ./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?
>>>
>>>
>>> On Monday, 21 December 2015 19:39:44 UTC+11, Aymeric Augustin wrote:

 2015-12-20 22:57 GMT+01:00 Cristiano Coelho :

> Thanks for the suggestion, I think that work around might just add too 
> much code, so I'm probably going the way of converting every datetime 
> column of every table to datetime(6) and afford the extra storage (and 
> probably a little performance impact ?).
> I think the documented change might need a little more of attention, 
> and mention something about that any equality query will stop working if 
> you either don't strip microseconds or update datetime columns to 
> datetime(6) (and not even datetime(3) will work...)
>

 If that's the solution we end up recommending -- because the horse has 
 left the barn months ago... -- then we must document it in detail.

 This is a large backwards incompatibility that may result in subtle 
 bugs and requires non-trivial steps to fix. It doesn't live up to Django's 
 standards.

 -- 
 Aymeric.

>>>

-- 
You received this message bec

Re: [Question] MySQL Microseconds stripping

[Adam Johnson]

Hi,

I've just finished converting all the datetime columns to datetime(6) in 
our database after an upgrade to MySQL 5.6. We don't use Django migrations, 
many of these were done manually with *pt-online-schema-change*. Just 
catching up on this thread, throwing some ideas in.

Why not strip the microseconds explicitly as soon as you're handed a datetime 
> with microseconds? 


A note here: stripping isn't what MySQL does - it actually *rounds*. This 
is the most annoying thing since it breaks a 5.6 replica feeding off a 5.5 
master - it's reported as unfixed bug here: 
https://bugs.mysql.com/bug.php?id=76948 . (Note: MariaDB kept the stripping 
behaviour).

We actually ended up patchy.patch ing 
MySQLdb.times.DateTime2literal so that microseconds never escaped the 
Python layer in the whole application, whilst we upgraded:

from MySQLdb.times import DateTime2literal
patchy.patch(DateTime2literal, """\
@@ -1,3 +1,4 @@
 def DateTime2literal(d, c):
 \"""Format a DateTime object as an ISO timestamp.\"""
+d = d.replace(microsecond=0)
 return string_literal(format_TIMESTAMP(d), c)
""")

converting every datetime column of every table to datetime(6) and afford 
> the extra storage (and probably


There's no extra storage usage in fact - the old format of datetime columns 
in 5.5 was less efficient than the new one in 5.6. Also, whenever you 
migrate a table that was created on 5.5 with datetime columns on 5.6, they 
get converted to the new format automatically. 

Does Django have visibility of the field constraints at insert/select 
> queryset time? 
>

Django could lookup the precision of datetime columns from 
*information_schema.columns* , but I don't think it's necessarily a great 
idea to change the field behaviour to match this.

./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?
>

If you're imagining a command to generate migrations, you can't do this for 
third party apps. It might be better to have a command that simply outputs, 
or runs, the SQL to alter all tables, outside of the migrations framework? 
I'd've liked that, I ended up using a little script like that here to 
generate the SQL and then run it manually/with *pt-online-schema-change*. 
I'll look at open sourcing it in Django-MySQL so you guys can look at it.

On Tuesday, December 22, 2015 at 2:56:41 AM UTC, Cristiano Coelho wrote:
>
> I think a simple setting allowing to use the old behaviour should be 
> enough, shouldn't it? How does it handle other db backends? I'm not sure if 
> oracle has an option for datetime precision, but if it does, it makes sense 
> to have a global setting for datetime precision, as right now you are 
> pretty much forced to always go with a precision of 6 (at least on mysql?) 
> and that might be just too much if you want a simpler datetime.
>
> El lunes, 21 de diciembre de 2015, 19:54:29 (UTC-3), Josh Smeaton escribió:
>>
>> I think this is a fairly big oversight that should be fixed in the most 
>> backwards compatible way, so users don't need to change their code, or only 
>> have to change it minimally. I'm with Aymeric here. Does Django have 
>> visibility of the field constraints at insert/select queryset time? Ideally 
>> Django would handle the differences transparently. If that's not possible 
>> then we should have a migration or script that'll do the conversion on 
>> behalf of users once off.
>>
>> ./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?
>>
>>
>> On Monday, 21 December 2015 19:39:44 UTC+11, Aymeric Augustin wrote:
>>>
>>> 2015-12-20 22:57 GMT+01:00 Cristiano Coelho :
>>>
 Thanks for the suggestion, I think that work around might just add too 
 much code, so I'm probably going the way of converting every datetime 
 column of every table to datetime(6) and afford the extra storage (and 
 probably a little performance impact ?).
 I think the documented change might need a little more of attention, 
 and mention something about that any equality query will stop working if 
 you either don't strip microseconds or update datetime columns to 
 datetime(6) (and not even datetime(3) will work...)

>>>
>>> If that's the solution we end up recommending -- because the horse has 
>>> left the barn months ago... -- then we must document it in detail.
>>>
>>> This is a large backwards incompatibility that may result in subtle bugs 
>>> and requires non-trivial steps to fix. It doesn't live up to Django's 
>>> standards.
>>>
>>> -- 
>>> Aymeric.
>>>
>>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To vie

Re: [Question] MySQL Microseconds stripping

[Cristiano Coelho]

I think a simple setting allowing to use the old behaviour should be 
enough, shouldn't it? How does it handle other db backends? I'm not sure if 
oracle has an option for datetime precision, but if it does, it makes sense 
to have a global setting for datetime precision, as right now you are 
pretty much forced to always go with a precision of 6 (at least on mysql?) 
and that might be just too much if you want a simpler datetime.

El lunes, 21 de diciembre de 2015, 19:54:29 (UTC-3), Josh Smeaton escribió:
>
> I think this is a fairly big oversight that should be fixed in the most 
> backwards compatible way, so users don't need to change their code, or only 
> have to change it minimally. I'm with Aymeric here. Does Django have 
> visibility of the field constraints at insert/select queryset time? Ideally 
> Django would handle the differences transparently. If that's not possible 
> then we should have a migration or script that'll do the conversion on 
> behalf of users once off.
>
> ./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?
>
>
> On Monday, 21 December 2015 19:39:44 UTC+11, Aymeric Augustin wrote:
>>
>> 2015-12-20 22:57 GMT+01:00 Cristiano Coelho :
>>
>>> Thanks for the suggestion, I think that work around might just add too 
>>> much code, so I'm probably going the way of converting every datetime 
>>> column of every table to datetime(6) and afford the extra storage (and 
>>> probably a little performance impact ?).
>>> I think the documented change might need a little more of attention, and 
>>> mention something about that any equality query will stop working if you 
>>> either don't strip microseconds or update datetime columns to datetime(6) 
>>> (and not even datetime(3) will work...)
>>>
>>
>> If that's the solution we end up recommending -- because the horse has 
>> left the barn months ago... -- then we must document it in detail.
>>
>> This is a large backwards incompatibility that may result in subtle bugs 
>> and requires non-trivial steps to fix. It doesn't live up to Django's 
>> standards.
>>
>> -- 
>> Aymeric.
>>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/78eb038d-a11f-47ec-bf85-ef454341af01%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Josh Smeaton]

I think this is a fairly big oversight that should be fixed in the most 
backwards compatible way, so users don't need to change their code, or only 
have to change it minimally. I'm with Aymeric here. Does Django have 
visibility of the field constraints at insert/select queryset time? Ideally 
Django would handle the differences transparently. If that's not possible 
then we should have a migration or script that'll do the conversion on 
behalf of users once off.

./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?


On Monday, 21 December 2015 19:39:44 UTC+11, Aymeric Augustin wrote:
>
> 2015-12-20 22:57 GMT+01:00 Cristiano Coelho  >:
>
>> Thanks for the suggestion, I think that work around might just add too 
>> much code, so I'm probably going the way of converting every datetime 
>> column of every table to datetime(6) and afford the extra storage (and 
>> probably a little performance impact ?).
>> I think the documented change might need a little more of attention, and 
>> mention something about that any equality query will stop working if you 
>> either don't strip microseconds or update datetime columns to datetime(6) 
>> (and not even datetime(3) will work...)
>>
>
> If that's the solution we end up recommending -- because the horse has 
> left the barn months ago... -- then we must document it in detail.
>
> This is a large backwards incompatibility that may result in subtle bugs 
> and requires non-trivial steps to fix. It doesn't live up to Django's 
> standards.
>
> -- 
> Aymeric.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/126940e1-805f-4bf8-8f74-8cdccb83182c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Aymeric Augustin]

2015-12-20 22:57 GMT+01:00 Cristiano Coelho :

> Thanks for the suggestion, I think that work around might just add too
> much code, so I'm probably going the way of converting every datetime
> column of every table to datetime(6) and afford the extra storage (and
> probably a little performance impact ?).
> I think the documented change might need a little more of attention, and
> mention something about that any equality query will stop working if you
> either don't strip microseconds or update datetime columns to datetime(6)
> (and not even datetime(3) will work...)
>

If that's the solution we end up recommending -- because the horse has left
the barn months ago... -- then we must document it in detail.

This is a large backwards incompatibility that may result in subtle bugs
and requires non-trivial steps to fix. It doesn't live up to Django's
standards.

-- 
Aymeric.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CANE-7mUbPJVxwspfK4i1J85rJfxNXMxFTjyYDWs69e6LEG21MA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Cristiano Coelho]

Thanks for the suggestion, I think that work around might just add too much 
code, so I'm probably going the way of converting every datetime column of 
every table to datetime(6) and afford the extra storage (and probably a 
little performance impact ?).
I think the documented change might need a little more of attention, and 
mention something about that any equality query will stop working if you 
either don't strip microseconds or update datetime columns to datetime(6) 
(and not even datetime(3) will work...)


El domingo, 20 de diciembre de 2015, 6:48:20 (UTC-3), Erik Cederstrand 
escribió:
>
>
> > Den 20. dec. 2015 kl. 01.04 skrev Cristiano Coelho  >: 
> > 
> > About using a custom datetime field that strips microseconds, that won't 
> work for raw queries I believe, not even .update statements as they ignore 
> pre-save? As the stripping happens (or used to happen) at the sql query 
> compile level. 
> > This is really a bummer, because it seems like the only option is to 
> convert all my datetime columns into datetime(6), which increases the table 
> size and index by around 10%, for something I will never use. Any other 
> work around that can work with both normal and raw queries? 
>
> While I understand that you'd like this to Just Work, you're sending 
> microseconds to the DB, knowing they will get lost, and expecting 
> comparisons to still work *with* microseconds. It's like expecting 12.34 == 
> int(12.34). 
>
> Why not strip the microseconds explicitly as soon as you're handed a 
> datetime with microseconds? That way you make it explicit that you really 
> don't want microseconds. That's less head-scratching for the next person to 
> work with your code. Just dt.replace(microsecond=0) all date values before 
> you issue a .filter(), .save(), .update(), .raw() or whatever. 
>
> > Should I complain at mysql forums? 
>
> You could try, but since Oracle took over, all my reports have been 
> answered with WONTFIX. Anyway, it'll be months or years before you get 
> something you can install on your server. 
>
> Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/fc7b50a9-def6-409b-b75c-9c40c616731d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Shai Berger]

On Sunday 20 December 2015 11:47:54 Erik Cederstrand wrote:
> 
> Why not strip the microseconds explicitly as soon as you're handed a
> datetime with microseconds? That way you make it explicit that you really
> don't want microseconds. That's less head-scratching for the next person
> to work with your code. Just dt.replace(microsecond=0) all date values
> before you issue a .filter(), .save(), .update(), .raw() or whatever.
> 

This could be a workable solution, or you could be asking him to add dozens of 
lines of boilerplate. Either way, we should contend with the claim of 
backwards-incompatibility. IMO we need to do one of two things:

- Provide a way to restore pre-1.8 behavior of the MySql backend, or
- Document a sensible workaround (and I do not consider "add a line before 
every query" to be sensible).

Shai.

Re: [Question] MySQL Microseconds stripping

[Erik Cederstrand]

> Den 20. dec. 2015 kl. 01.04 skrev Cristiano Coelho :
> 
> About using a custom datetime field that strips microseconds, that won't work 
> for raw queries I believe, not even .update statements as they ignore 
> pre-save? As the stripping happens (or used to happen) at the sql query 
> compile level.
> This is really a bummer, because it seems like the only option is to convert 
> all my datetime columns into datetime(6), which increases the table size and 
> index by around 10%, for something I will never use. Any other work around 
> that can work with both normal and raw queries? 

While I understand that you'd like this to Just Work, you're sending 
microseconds to the DB, knowing they will get lost, and expecting comparisons 
to still work *with* microseconds. It's like expecting 12.34 == int(12.34).

Why not strip the microseconds explicitly as soon as you're handed a datetime 
with microseconds? That way you make it explicit that you really don't want 
microseconds. That's less head-scratching for the next person to work with your 
code. Just dt.replace(microsecond=0) all date values before you issue a 
.filter(), .save(), .update(), .raw() or whatever.

> Should I complain at mysql forums?

You could try, but since Oracle took over, all my reports have been answered 
with WONTFIX. Anyway, it'll be months or years before you get something you can 
install on your server.

Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/0001EA1D-0B44-4CC8-AC6B-A80F3B14F943%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Cristiano Coelho]

Aymeric is right. I do an insert with microseconds (since that's what 
django does right now) but mysql has the column defined as datetime(0), so 
it just strips the microsecond part, however, when doing the select, I'm 
expecting to get the value I have just inserted, but it doesn't work, since 
mysql doesn't strip microseconds from the select as it does for the insert. 
So this is really a mysql issue I guess...

About using a custom datetime field that strips microseconds, that won't 
work for raw queries I believe, not even .update statements as they ignore 
pre-save? As the stripping happens (or used to happen) at the sql query 
compile level.
This is really a bummer, because it seems like the only option is to 
convert all my datetime columns into datetime(6), which increases the table 
size and index by around 10%, for something I will never use.

Any other work around that can work with both normal and raw queries? 
Should I complain at mysql forums?

El sábado, 19 de diciembre de 2015, 7:39:12 (UTC-3), Shai Berger escribió:
>
> On Saturday 19 December 2015 11:23:17 Erik Cederstrand wrote: 
> > > Den 19. dec. 2015 kl. 16.01 skrev Aymeric Augustin 
> > > >: 
> > > 
> > > To be fair, this has a lot to do with MySQL’s lax approach to storing 
> > > data. There’s so many situations where it just throws data away 
> happily 
> > > that one can’t really expect to read back data written to MySQL. 
> > > 
> > > That said, this change in Django sets up this trap for unsuspecting 
> > > users. Providing a way for users to declare which fields use the old 
> > > format won't work because of pluggable apps: they cannot know what 
> > > version of MySQL their users were running when they first created a 
> > > given datetime column. The best solution may be to provide a 
> conversion 
> > > script to upgrade all datetime columns from the old to the new format. 
> > 
> > One simple solution could be for Christiano to subclass the 
> DateTimeField 
> > to handle the microsecond precision explicitly. Something like this to 
> > strip: 
> > 
> > 
> > class DateTimeFieldWithPrecision(DateTimeField): 
> >def __init__(self, *args, **kwargs): 
> >self.precision = kwargs.get('precision', 6) 
> >assert 0 <= self.precision <= 6 
> > super().__init__(*args, **kwargs) 
> > 
> >def pre_save(self, model_instance, add): 
> >dt = getattr(model_instance, self.attname) 
> > 
> dt.replace(microsecond=int(dt.microsecond/10**(6-self.precision))) 
> >return dt 
> > 
>
> if I get the complaints correctly, something similar would need to be done 
> when preparing a value for querying. 
>
> More generally, I think Christiano just wants "the old field back" -- so, 
> he 
> has a use-case for a DateTimeField which explicitly does not use second 
> fractions. We already have a DateTimeField which explicitly does not use 
> day 
> fractions (DateField), so I suppose we could find sense in that... We 
> would 
> typically suggest, as Erik implicitly did, that such field be done outside 
> of 
> Django, but the backward-compatibility issues mentioned by Aymeric make it 
> quite plausible that such a field will be added to core or contrib. 
>
> Shai. 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/78d8b177-5dbe-4ad1-a86f-063332591da7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Shai Berger]

On Saturday 19 December 2015 11:23:17 Erik Cederstrand wrote:
> > Den 19. dec. 2015 kl. 16.01 skrev Aymeric Augustin
> > :
> > 
> > To be fair, this has a lot to do with MySQL’s lax approach to storing
> > data. There’s so many situations where it just throws data away happily
> > that one can’t really expect to read back data written to MySQL.
> > 
> > That said, this change in Django sets up this trap for unsuspecting
> > users. Providing a way for users to declare which fields use the old
> > format won't work because of pluggable apps: they cannot know what
> > version of MySQL their users were running when they first created a
> > given datetime column. The best solution may be to provide a conversion
> > script to upgrade all datetime columns from the old to the new format.
> 
> One simple solution could be for Christiano to subclass the DateTimeField
> to handle the microsecond precision explicitly. Something like this to
> strip:
> 
> 
> class DateTimeFieldWithPrecision(DateTimeField):
>def __init__(self, *args, **kwargs):
>self.precision = kwargs.get('precision', 6)
>assert 0 <= self.precision <= 6
>   super().__init__(*args, **kwargs)
> 
>def pre_save(self, model_instance, add):
>dt = getattr(model_instance, self.attname)
>   dt.replace(microsecond=int(dt.microsecond/10**(6-self.precision)))
>return dt
> 

if I get the complaints correctly, something similar would need to be done 
when preparing a value for querying.

More generally, I think Christiano just wants "the old field back" -- so, he 
has a use-case for a DateTimeField which explicitly does not use second 
fractions. We already have a DateTimeField which explicitly does not use day 
fractions (DateField), so I suppose we could find sense in that... We would 
typically suggest, as Erik implicitly did, that such field be done outside of 
Django, but the backward-compatibility issues mentioned by Aymeric make it 
quite plausible that such a field will be added to core or contrib.

Shai.

Re: [Question] MySQL Microseconds stripping

[Erik Cederstrand]

> Den 19. dec. 2015 kl. 16.01 skrev Aymeric Augustin 
> :
> 
> To be fair, this has a lot to do with MySQL’s lax approach to storing data. 
> There’s so many situations where it just throws data away happily that one 
> can’t really expect to read back data written to MySQL.
> 
> That said, this change in Django sets up this trap for unsuspecting users. 
> Providing a way for users to declare which fields use the old format won't 
> work because of pluggable apps: they cannot know what version of MySQL their 
> users were running when they first created a given datetime column. The best 
> solution may be to provide a conversion script to upgrade all datetime 
> columns from the old to the new format.

One simple solution could be for Christiano to subclass the DateTimeField to 
handle the microsecond precision explicitly. Something like this to strip:


class DateTimeFieldWithPrecision(DateTimeField):
   def __init__(self, *args, **kwargs):
   self.precision = kwargs.get('precision', 6)
   assert 0 <= self.precision <= 6
super().__init__(*args, **kwargs)

   def pre_save(self, model_instance, add):
   dt = getattr(model_instance, self.attname)
dt.replace(microsecond=int(dt.microsecond/10**(6-self.precision)))
   return dt


Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/24D67D81-5EB2-4E0A-B7FB-2771DA2FBEEB%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Aymeric Augustin]

Hello,

> On 19 déc. 2015, at 09:21, Erik Cederstrand  wrote:
> 
>> Den 19. dec. 2015 kl. 13.15 skrev Cristiano Coelho 
>> :
>> 
>> The issue is that every datetime column created has no microseconds (since 
>> they were created with django 1.7, so it is actually a datetime(0) column) 
>> and I would like to keep it that way, however, since django 1.8+ will always 
>> send microseconds in the query, inserts will go fine (mysql will just ignore 
>> them) but SELECTS will all fail since mysql will not strip microseconds from 
>> the where clause (even if the column is defined as 0 datetime, duh, really 
>> mysql?), so basically everything that uses datetime equaility in the query 
>> stopped working.
> 
> Can you elaborate on that? You're doing something like:
> 
>  SELECT foo_date FROM my_table WHERE foo_date = '2015-12-24 12:34:56.123456';
> 
> and expecting it to return rows where foo_date is '2015-12-24 12:34:56', but 
> it doesn't?
> 
> I'm not sure that's a bug - it's not the least astonishing to me. Why aren't 
> you stripping microseconds from the datetime values before issuing the query, 
> if your data never has microseconds?


If I read Cristiano correctly, he does this:

INSERT INTO my_table (foo_date) VALUES ('2015-12-24 12:34:56.123456');
SELECT foo_date FROM my_table WHERE foo_date = '2015-12-24 12:34:56.123456’;

and he expects it to return the row he just inserted. That wouldn’t be 
astonishing. In my opinions, either the insert should fail on the select should 
return the inserted row. (Obviously this isn’t what happens. I kno\  we’re 
talking about MySQL here.)


The relevant commits landed in 1.8 which was released in April:

https://github.com/django/django/commit/9e746c13e81241fbf1ae64ec118edaa491790046
https://github.com/django/django/commit/22da5f88173917bcf8a652dce84f382c9202

The release notes say that “new datetime database columns created with Django 
1.8 and MySQL 5.6.4 and up will support microseconds”. This means that users 
end up with “mixed” databases where some date time columns have fractional 
microseconds and others have not. I don’t think that’s a very good outcome.

I had brought up that question on the ticket, 
https://code.djangoproject.com/ticket/19716#comment:3 : “what's going to happen 
for developers upgrading from an older version of Django?” but I didn’t follow 
up, unfortunately.


Depending on how MySQL handles conversions, this can easily result in bad 
behavior. For example, let’s assume the following model:

class Foo(models.Model):

updated_at = models.DateTimeField()  # field created on old Django or MySQL
admin_updated_at = models.DateTimeField(blank=True, null=True)  # field 
created on newer Django or MySQL

@property
def last_updated_by_admin(self):
return updated_at == admin_updated_at

foo = Foo.objects.get(…)
foo.updated_at = foo.admin_updated_at = timezone.now()
foo.save()
foo.last_updated_by_admin  # True

foo = Foo.objects.get(…)
foo.last_updated_by_admin  # False


To be fair, this has a lot to do with MySQL’s lax approach to storing data. 
There’s so many situations where it just throws data away happily that one 
can’t really expect to read back data written to MySQL.

That said, this change in Django sets up this trap for unsuspecting users. 
Providing a way for users to declare which fields use the old format won't work 
because of pluggable apps: they cannot know what version of MySQL their users 
were running when they first created a given datetime column. The best solution 
may be to provide a conversion script to upgrade all datetime columns from the 
old to the new format.


Best regards,

-- 
Aymeric.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/D1BE240D-C89C-4498-91F6-A48C44AFF4B2%40polytechnique.org.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Erik Cederstrand]

> Den 19. dec. 2015 kl. 13.15 skrev Cristiano Coelho :
> 
> Erik,
> I'm using MySQL 5.6.x and indeed it has microseconds support, but that's not 
> the issue.
> 
> The issue is that every datetime column created has no microseconds (since 
> they were created with django 1.7, so it is actually a datetime(0) column) 
> and I would like to keep it that way, however, since django 1.8+ will always 
> send microseconds in the query, inserts will go fine (mysql will just ignore 
> them) but SELECTS will all fail since mysql will not strip microseconds from 
> the where clause (even if the column is defined as 0 datetime, duh, really 
> mysql?), so basically everything that uses datetime equaility in the query 
> stopped working.

Can you elaborate on that? You're doing something like:

  SELECT foo_date FROM my_table WHERE foo_date = '2015-12-24 12:34:56.123456';

and expecting it to return rows where foo_date is '2015-12-24 12:34:56', but it 
doesn't?

I'm not sure that's a bug - it's not the least astonishing to me. Why aren't 
you stripping microseconds from the datetime values before issuing the query, 
if your data never has microseconds?

Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/9F95A9A1-89C5-4304-AF98-8D76FD09C2DC%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Cristiano Coelho]

Erik,
I'm using MySQL 5.6.x and indeed it has microseconds support, but that's 
not the issue.

The issue is that every datetime column created has no microseconds (since 
they were created with django 1.7, so it is actually a datetime(0) column) 
and I would like to keep it that way, however, since django 1.8+ will 
always send microseconds in the query, inserts will go fine (mysql will 
just ignore them) but SELECTS will all fail since mysql will not strip 
microseconds from the where clause (even if the column is defined as 0 
datetime, duh, really mysql?), so basically everything that uses datetime 
equaility in the query stopped working.

As suggested, all datetime columns should be updated to datetime(6) so it 
works correctly with the new django behaviour, however, I would like to 
keep datetime columns as they are, since I don't need microseconds, so I'm 
wondering if there's any way to get back the old django behaviour for 
mysql, through a setting, or monkey patch (as long as it works for all 
models and raw queries).

El sábado, 19 de diciembre de 2015, 1:59:00 (UTC-3), Erik Cederstrand 
escribió:
>
>
> > Den 19. dec. 2015 kl. 07.52 skrev Cristiano Coelho  >: 
> > 
> > Hello, 
> > 
> > After django 1.8, the mysql backend no longer strips microseconds. 
> > This is giving me some issues when upgrading from 1.7 (I actually 
> upgraded to 1.9 directly), since date times are not stored with micro 
> second precision on mysql, but the queries are sent with them. 
> > As I see it, my only option is to update all existing date time columns 
> of all existing tables, which is quite boring since there are many tables. 
> > Is there a way I can explicitly set the model datetime precision? Will 
> this work with raw queries also? Could this be a global setting or monkey 
> patch? This new behaviour basically breaks any '=' query on date times, at 
> least raw queries (I haven't tested the others) since it sends micro 
> seconds which are not stripped down. 
>
> MySQL as of version 5.6.4 (and MariaDB) is able to store microseconds in 
> datetime fields, but you need to set the precision when you create the 
> column. In Django, this should "just work". Which version of MySQL are you 
> using, and are your columns created as DATETIME(6)? 
>
> Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/2229a467-19d6-4e2f-b510-d49eb6ccf405%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Erik Cederstrand]

> Den 19. dec. 2015 kl. 07.52 skrev Cristiano Coelho :
> 
> Hello,
> 
> After django 1.8, the mysql backend no longer strips microseconds. 
> This is giving me some issues when upgrading from 1.7 (I actually upgraded to 
> 1.9 directly), since date times are not stored with micro second precision on 
> mysql, but the queries are sent with them.
> As I see it, my only option is to update all existing date time columns of 
> all existing tables, which is quite boring since there are many tables.
> Is there a way I can explicitly set the model datetime precision? Will this 
> work with raw queries also? Could this be a global setting or monkey patch? 
> This new behaviour basically breaks any '=' query on date times, at least raw 
> queries (I haven't tested the others) since it sends micro seconds which are 
> not stripped down.

MySQL as of version 5.6.4 (and MariaDB) is able to store microseconds in 
datetime fields, but you need to set the precision when you create the column. 
In Django, this should "just work". Which version of MySQL are you using, and 
are your columns created as DATETIME(6)?

Erik

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/8BB7E576-385E-41D7-B0AB-CBF4DB17ED36%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.

Re: [Question] MySQL Microseconds stripping

[Cristiano Coelho]

Also I would like to add, that even if the mysql column is a 0 fractional 
datetime column, and you send a datetime with some fraction in it (which is 
what django does right now), it won't handle it correctly (ie trim the 
fraction since the actual column has no fraction) but instead just try to 
match the fractional date. This makes me think if this might be a bug with 
mysql...

El viernes, 18 de diciembre de 2015, 21:52:43 (UTC-3), Cristiano Coelho 
escribió:
>
> Hello,
>
> After django 1.8, the mysql backend no longer strips microseconds. 
> This is giving me some issues when upgrading from 1.7 (I actually upgraded 
> to 1.9 directly), since date times are not stored with micro second 
> precision on mysql, but the queries are sent with them.
> As I see it, my only option is to update all existing date time columns of 
> all existing tables, which is quite boring since there are many tables.
> Is there a way I can explicitly set the model datetime precision? Will 
> this work with raw queries also? Could this be a global setting or monkey 
> patch? This new behaviour basically breaks any '=' query on date times, at 
> least raw queries (I haven't tested the others) since it sends micro 
> seconds which are not stripped down.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/888f41c7-d2b8-4b86-b2cd-e5ff033f5cfa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Question] MySQL Microseconds stripping

[Cristiano Coelho]

Hello,

After django 1.8, the mysql backend no longer strips microseconds. 
This is giving me some issues when upgrading from 1.7 (I actually upgraded 
to 1.9 directly), since date times are not stored with micro second 
precision on mysql, but the queries are sent with them.
As I see it, my only option is to update all existing date time columns of 
all existing tables, which is quite boring since there are many tables.
Is there a way I can explicitly set the model datetime precision? Will this 
work with raw queries also? Could this be a global setting or monkey patch? 
This new behaviour basically breaks any '=' query on date times, at least 
raw queries (I haven't tested the others) since it sends micro seconds 
which are not stripped down.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/c95328b3-5e22-471b-bbb0-30924eca8363%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.