search

afsc-gap-products / gap_products

This repository supports code used to create tables in the GAP_PRODUCTS Oracle schema. These tables include the master production tables, tables shared with AKFIN, and tables publicly shared on FOSS.
https://afsc-gap-products.github.io/gap_products/
Creative Commons Zero v1.0 Universal
5 stars 5 forks source link

Change datatypes from DATE to DATETIME #19

Closed EmilyMarkowitz-NOAA closed 5 months ago

EmilyMarkowitz-NOAA commented 6 months ago

Issue Description

AKFIN_HAUL.DATE_TIME and FOSS_HAUL.DATE_TIME are currently data type DATE (thus only recording the day a haul was done, not also the time), but should be in the DATETIME data type. "The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'."

Resolve

I've changed it in the METADATA_COLUMN tab of the future oracle spreadsheet so it will be automatically implemented on the next run.

EmilyMarkowitz-NOAA commented 6 months ago

I added the following metadata text to the DATE_TIME, DATE_TIME_START, and DATE_TIME_END columns: "All dates and times are in Alaska time (AKDT) of Anchorage, AK, USA (UTC/GMT -8 hours). " Feel free to improve/change.

zoyafuso-NOAA commented 5 months ago

Repeating the comment in the last commit here: DATETIME is apparetnly not a supported datatype in SQL Developer, opting for TIMESTAMP instead. I set the time zone as UTC-8 in the syntax of the query. Hopefully that will resolve and close this issue.

EmilyMarkowitz-NOAA commented 5 months ago

Spoke with the FOSS team and they were actually able to extract time from the DATE data type. I am not exactly sure why/how that works, but said that they would prefer to keep the data column in the DATE data type instead of TIMESTAMP. I'm changing it back to DATE in METADATA_COLUMN tab of the future oracle google spreadsheet. Sounds like time is still embedded in the DATE field somehow... despite all of this discussion(?)

zoyafuso-NOAA commented 5 months ago

No problem. @EmilyMarkowitz-NOAA, do you mind reverting this commit b6bf1b3 from above for me? I'm not sure how to do that on the website. On the next run (this weekend) this change will be incorporated.

EmilyMarkowitz-NOAA commented 5 months ago

Reverted! Thanks, @zoyafuso-NOAA !

zoyafuso-NOAA commented 5 months ago

@EmilyMarkowitz-NOAA , the DATE_TIME field in GAP_PRODUCTS.FOSS_HAUL is now back to DATE format. Check to confirm and I'll leave you to close the comment.

EmilyMarkowitz-NOAA commented 5 months ago

Confrimed! Thanks, @zoyafuso-NOAA !