Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MySQL DATETIME with microseconds precision, not displaying #1309

Closed
altmannmarcelo opened this issue Jul 3, 2024 · 0 comments
Closed

MySQL DATETIME with microseconds precision, not displaying #1309

altmannmarcelo opened this issue Jul 3, 2024 · 0 comments
Assignees
@altmannmarcelo

Comments

@altmannmarcelo
Copy link
Contributor

Description

If you have a field with DATETIME(6) and the value inserted is 000000 we do not display it.

create table t (dt datetime(6));
insert into t values ('2000-01-01 00:00:00.000001'),  ('2000-01-02 00:00:00.000000');
CREATE CACHE FROM SELECT * FROM t;
mysql> SELECT * FROM t;
+----------------------------+
| dt                         |
+----------------------------+
| 2000-01-01 00:00:00.000001 |
| 2000-01-02 00:00:00        |
+----------------------------+
2 rows in set (0,00 sec)

Change in user-visible behavior

Requires documentation change
@altmannmarcelo altmannmarcelo self-assigned this Jul 3, 2024
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
mysql_srv: output mysql datetime/timestas as TimestampTZ
08e827a 
We have out own implementation of Datetime/Timestamp called
TimestampTZ. This object has a 3 bytes bitmap that gives us better
control over printing date only, timestamp with tz even if tz is zero
and microsecond precision even if it is zero.
Currently we were converting the TimestampTZ to NaiveDateTime and then
printing it. This was causing some issues with MySQL and datetime.
NaiveDateTime has an inner object called NaiveTime to represent time.
When printing naive time, if the microsecond is zero, it is not
printed [0]. For MySQL, if the Datetime column has the optional
microsecond precision set, we need to print the microsecond even if
it is zero, causing a mismatch between the readyset and MySQL.

This commit changes the display object for datetime/tz columns to
TimestampTZ and implement text and binary protocol trait for it.

Ref: REA-4490
Ref: #1309

[0]: https://github.com/chronotope/chrono/blob/v0.4.38/src/naive/time/mod.rs#L1520

Change-Id: I31301b20bebdd1bb33dbf6b79b84a8f7065dee80
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
replicators: Fix DATETIME microsecond precision
b0e775e 
This commit fixes the microsecond precision of DATETIME columns in
MySQL by setting the correct fractional seconds precision in the
TimestampTZ object.

This fixes a discrepancy between the precision of the DATETIME in
Readyset and MySQL.

Fixes: REA-4469
Fixes: #1309

Release-Note-Core: Fixes the microsecond precision of DATETIME columns
  in MySQL that sometimes were not being correctly represented in
  Readyset.

Change-Id: Ifc3bb58b16a87423a0e4079dffa34ed28fafaa35
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
mysql_srv: output mysql datetime/timestas as TimestampTZ
268597e 
We have out own implementation of Datetime/Timestamp called
TimestampTZ. This object has a 3 bytes bitmap that gives us better
control over printing date only, timestamp with tz even if tz is zero
and microsecond precision even if it is zero.
Currently we were converting the TimestampTZ to NaiveDateTime and then
printing it. This was causing some issues with MySQL and datetime.
NaiveDateTime has an inner object called NaiveTime to represent time.
When printing naive time, if the microsecond is zero, it is not
printed [0]. For MySQL, if the Datetime column has the optional
microsecond precision set, we need to print the microsecond even if
it is zero, causing a mismatch between the readyset and MySQL.

This commit changes the display object for datetime/tz columns to
TimestampTZ and implement text and binary protocol trait for it.

Ref: REA-4490
Ref: #1309

[0]: https://github.com/chronotope/chrono/blob/v0.4.38/src/naive/time/mod.rs#L1520

Change-Id: I31301b20bebdd1bb33dbf6b79b84a8f7065dee80
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
replicators: Fix DATETIME microsecond precision
69b0424 
This commit fixes the microsecond precision of DATETIME columns in
MySQL by setting the correct fractional seconds precision in the
TimestampTZ object.

This fixes a discrepancy between the precision of the DATETIME in
Readyset and MySQL.

Fixes: REA-4469
Fixes: #1309

Release-Note-Core: Fixes the microsecond precision of DATETIME columns
  in MySQL that sometimes were not being correctly represented in
  Readyset.

Change-Id: Ifc3bb58b16a87423a0e4079dffa34ed28fafaa35
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
replicators: Fix DATETIME microsecond precision
e392373 
This commit fixes the microsecond precision of DATETIME columns in
MySQL by setting the correct fractional seconds precision in the
TimestampTZ object.

This fixes a discrepancy between the precision of the DATETIME in
Readyset and MySQL.

Fixes: REA-4469
Fixes: #1309

Release-Note-Core: Fixes the microsecond precision of DATETIME columns
  in MySQL that sometimes were not being correctly represented in
  Readyset.

Change-Id: Ifc3bb58b16a87423a0e4079dffa34ed28fafaa35
readysetbot pushed a commit that referenced this issue Jul 9, 2024
@altmannmarcelo
mysql_srv: output mysql datetime/timestas as TimestampTZ
891998c 
We have out own implementation of Datetime/Timestamp called
TimestampTZ. This object has a 3 bytes bitmap that gives us better
control over printing date only, timestamp with tz even if tz is zero
and microsecond precision even if it is zero.
Currently we were converting the TimestampTZ to NaiveDateTime and then
printing it. This was causing some issues with MySQL and datetime.
NaiveDateTime has an inner object called NaiveTime to represent time.
When printing naive time, if the microsecond is zero, it is not
printed [0]. For MySQL, if the Datetime column has the optional
microsecond precision set, we need to print the microsecond even if
it is zero, causing a mismatch between the readyset and MySQL.

This commit changes the display object for datetime/tz columns to
TimestampTZ and implement text and binary protocol trait for it.

Ref: REA-4490
Ref: #1309

[0]: https://github.com/chronotope/chrono/blob/v0.4.38/src/naive/time/mod.rs#L1520

Change-Id: I31301b20bebdd1bb33dbf6b79b84a8f7065dee80
Reviewed-on: https://gerrit.readyset.name/c/readyset/+/7655
Tested-by: Buildkite CI
Reviewed-by: Michael Zink <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@altmannmarcelo altmannmarcelo

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@altmannmarcelo