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

Exporting date values changes the year #40306

Closed
hs-nborjanovic opened this issue Mar 19, 2024 · 1 comment · Fixed by #40410
Closed

Exporting date values changes the year #40306

hs-nborjanovic opened this issue Mar 19, 2024 · 1 comment · Fixed by #40410
Assignees
@adam-james-v
Labels
.Correctness Reporting/Export .Team/DashViz Dashboard and Viz team Type:Bug Product defects
Milestone
0.49.2

Comments

@hs-nborjanovic
Copy link

Describe the bug

Error notced after metabase update to 0.49.0.
When exporting a report with a date of 12/31/2023 (in short format 'M/D/YYYY') to .csv or .json the year gets exported as 2024.
Oddly, the same error does not occur for 12/31/2022.

To Reproduce

  1. Run SQL Code that generates the date e.g. SELECT TO_DATE('20231231', 'YYYYMMDD')
  2. Set the Column Formatting - Date style as M/D/YYYY
  3. Export the value as .csv or .json

Expected behavior

To export the date with the accurate year.

Logs

No response

Information about your Metabase installation

- browser: Brave 1.63.174
- OS: Windows 11
- databases: Redshift, PostgreSQL
- Metabase version: 0.49.0
- hosting: AWS
- internal DB: PostgeSQL

Severity

impacting our data integrity

Additional context

No response
@perivamsi perivamsi added .Team/DashViz Dashboard and Viz team Reporting/Export and removed .Needs Triage labels Mar 19, 2024
@adam-james-v adam-james-v self-assigned this Mar 19, 2024
@mudge
Copy link

mudge commented Mar 20, 2024 •
edited
Loading

We have seen the same issue with a BigQuery DATE column with the value 31/12/2023 appearing correctly in the UI but exporting to CSV or JSON (but not XSLX) will produce 31/12/2024.

For reference:

  • Browser: Safari 17.4
  • OS: macOS 14.4
  • Database: BigQuery
  • Metabase version: 0.49.0
  • Hosting: Self-hosted on Heroku
  • Internal DB: PostgreSQL

Our BigQuery, PostgreSQL and server timezones are all set to UTC.

adam-james-v added a commit that referenced this issue Mar 20, 2024
@adam-james-v
Formatter Gives correct year for dates near start/end of Year
3f6f298 
Fixes: #40306

Our datetime formatter relies on the `java-time.api`, for which there are many different, sometimes confusing, formatter patterns: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatterBuilder.html#appendPattern-java.lang.String-

In this case, 'YYYY' is a week-of-year style year, which calculates which week a date falls into before returning the
year. Sometimes days near the start/end of a year will fall into a week in the wrong year.

For example, apparently 2023-12-31 falls into the 1st week of 2024, which probably not the year you'd expect to
see. What we probably do want is 'yyyy' which calculates what day of the year the date is and then returns the year
based off of that instead of the week number.

For an explanation, you can check out this SO answer: https://stackoverflow.com/a/46395342 provides an explanation.
@adam-james-v adam-james-v mentioned this issue Mar 20, 2024
Merged
@calherries calherries mentioned this issue Mar 21, 2024
Closed
adam-james-v added a commit that referenced this issue Mar 21, 2024
@adam-james-v
Formatter Gives correct year for dates near start/end of Year (#40410)
104f18c 
Fixes: #40306

Our datetime formatter relies on the `java-time.api`, for which there are many different, sometimes confusing, formatter patterns: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatterBuilder.html#appendPattern-java.lang.String-

In this case, 'YYYY' is a week-of-year style year, which calculates which week a date falls into before returning the
year. Sometimes days near the start/end of a year will fall into a week in the wrong year.

For example, apparently 2023-12-31 falls into the 1st week of 2024, which probably not the year you'd expect to
see. What we probably do want is 'yyyy' which calculates what day of the year the date is and then returns the year
based off of that instead of the week number.

For an explanation, you can check out this SO answer: https://stackoverflow.com/a/46395342 provides an explanation.
github-actions bot pushed a commit that referenced this issue Mar 21, 2024
@adam-james-v
Formatter Gives correct year for dates near start/end of Year (#40410)
b26e463 
Fixes: #40306

Our datetime formatter relies on the `java-time.api`, for which there are many different, sometimes confusing, formatter patterns: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatterBuilder.html#appendPattern-java.lang.String-

In this case, 'YYYY' is a week-of-year style year, which calculates which week a date falls into before returning the
year. Sometimes days near the start/end of a year will fall into a week in the wrong year.

For example, apparently 2023-12-31 falls into the 1st week of 2024, which probably not the year you'd expect to
see. What we probably do want is 'yyyy' which calculates what day of the year the date is and then returns the year
based off of that instead of the week number.

For an explanation, you can check out this SO answer: https://stackoverflow.com/a/46395342 provides an explanation.
metabase-bot bot added a commit that referenced this issue Mar 21, 2024
@metabase-bot @adam-james-v
Formatter Gives correct year for dates near start/end of Year (#40410) (
b523d0f 
#40450)

Fixes: #40306

Our datetime formatter relies on the `java-time.api`, for which there are many different, sometimes confusing, formatter patterns: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatterBuilder.html#appendPattern-java.lang.String-

In this case, 'YYYY' is a week-of-year style year, which calculates which week a date falls into before returning the
year. Sometimes days near the start/end of a year will fall into a week in the wrong year.

For example, apparently 2023-12-31 falls into the 1st week of 2024, which probably not the year you'd expect to
see. What we probably do want is 'yyyy' which calculates what day of the year the date is and then returns the year
based off of that instead of the week number.

For an explanation, you can check out this SO answer: https://stackoverflow.com/a/46395342 provides an explanation.

Co-authored-by: adam-james <21064735+adam-james-v@users.noreply.github.com>
@adam-james-v adam-james-v added this to the 0.49.2 milestone Mar 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@adam-james-v adam-james-v

Labels
.Correctness Reporting/Export .Team/DashViz Dashboard and Viz team Type:Bug Product defects
Projects
None yet
Milestone
0.49.2
Development

Successfully merging a pull request may close this issue.

Formatter Gives correct year for dates near start/end of Year metabase/metabase
5 participants
@mudge @perivamsi @adam-james-v @cbalusek @hs-nborjanovic