[Spike - API] Ensure all dates are UTC

[ekraffmiller]

Overview of the Feature Request This spike was triggered by a discussion of how to present dates in the SPA. We would like all dates being returned from the API to be UTC. The UI can convert those dates to the user's local timezone. Part of what is to be researched is how the dates are stored in Postgres, because that will affect the logic of APIs that handle dates. The current assumption is that all dates are stored as UTC, but we want to confirm that.

What kind of user is the feature intended for? (Example users roles: API User, Curator, Depositor, Guest, Superuser, Sysadmin) API User

What inspired the request? Logic for displaying dates in the SPA

What existing behavior do you want changed? Want to ensure all dates are returned as UTC

Any brand new behavior do you want to add to Dataverse?

Any open or closed issues related to this feature request?

[cmbz]

2023/10/10: Sized at 3 based on comments during resizing meeting.

[scolapasta]

Ran this query in the db, which returned all columns with a timestamp:

SELECT table_name, column_name, data_type FROM information_schema.columns WHERE data_type like 'timestamp%';

ignoring all system tables, the following results indicate that all our timestamps are saved without a timezone:

_dvn33_study | createtime | timestamp without time zone _dvn33_study | lastupdatetime | timestamp without time zone _dvn33_study | lastexporttime | timestamp without time zone _dvn33_study | lastindextime | timestamp without time zone _dvn33_studyversion | createtime | timestamp without time zone _dvn33_studyversion | releasetime | timestamp without time zone _dvn33_studyversion | lastupdatetime | timestamp without time zone _dvn33_studyversion | archivetime | timestamp without time zone _dvn33_versioncontributor | lastupdatetime | timestamp without time zone _dvn3_study | createtime | timestamp without time zone _dvn3_study | lastupdatetime | timestamp without time zone _dvn3_study | lastexporttime | timestamp without time zone _dvn3_study | lastindextime | timestamp without time zone _dvn3_studyversion | createtime | timestamp without time zone _dvn3_studyversion | releasetime | timestamp without time zone _dvn3_studyversion | lastupdatetime | timestamp without time zone _dvn3_studyversion | archivetime | timestamp without time zone _dvn3_vdc | releasedate | timestamp without time zone _dvn3_vdc | createddate | timestamp without time zone _dvn3_vdcnetwork | termsofuseupdated | timestamp without time zone _dvn3_vdcnetwork | networkcreated | timestamp without time zone _dvn3_versioncontributor | lastupdatetime | timestamp without time zone actionlogrecord | endtime | timestamp without time zone actionlogrecord | starttime | timestamp without time zone alternativepersistentidentifier | globalidcreatetime | timestamp without time zone apitoken | createtime | timestamp without time zone apitoken | expiretime | timestamp without time zone authenticateduser | emailconfirmed | timestamp without time zone authenticateduser | lastlogintime | timestamp without time zone authenticateduser | lastapiusetime | timestamp without time zone authenticateduser | deactivatedtime | timestamp without time zone authenticateduser | createdtime | timestamp without time zone clientharvestrun | finishtime | timestamp without time zone clientharvestrun | starttime | timestamp without time zone confirmemaildata | created | timestamp without time zone confirmemaildata | expires | timestamp without time zone customzipservicerequest | issuetime | timestamp without time zone dataset | lastexporttime | timestamp without time zone datasetlinkingdataverse | linkcreatetime | timestamp without time zone datasetlock | starttime | timestamp without time zone datasetversion | archivetime | timestamp without time zone datasetversion | createtime | timestamp without time zone datasetversion | lastupdatetime | timestamp without time zone datasetversion | releasetime | timestamp without time zone datasetversionuser | lastupdatedate | timestamp without time zone dataverselinkingdataverse | linkcreatetime | timestamp without time zone dvobject | createdate | timestamp without time zone dvobject | indextime | timestamp without time zone dvobject | modificationtime | timestamp without time zone dvobject | permissionindextime | timestamp without time zone dvobject | permissionmodificationtime | timestamp without time zone dvobject | publicationdate | timestamp without time zone dvobject | globalidcreatetime | timestamp without time zone externalvocabularyvalue | lastupdatedate | timestamp without time zone filedownload | downloadtimestamp | timestamp without time zone flyway_schema_history | installed_on | timestamp without time zone guestbook | createtime | timestamp without time zone guestbookresponse | responsetime | timestamp without time zone ingestreport | endtime | timestamp without time zone ingestreport | starttime | timestamp without time zone metric | lastcalleddate | timestamp without time zone oairecord | lastupdatetime | timestamp without time zone oauth2tokendata | expirydate | timestamp without time zone passwordresetdata | created | timestamp without time zone passwordresetdata | expires | timestamp without time zone template | createtime | timestamp without time zone userbannermessage | bannerdismissaltime | timestamp without time zone usernotification | senddate | timestamp without time zone workflowcomment | created | timestamp without time zone fileaccessrequests | creation_time | timestamp without time zone extractcitationdata | Publication Date | timestamp without time zone

[scolapasta]

So that said, the next step was to look for how we save or display these - there are 52 of them, but I wanted to find something that actually displays the time in the UI (as I don't think most do). I decided to look at harvesting runs.

So for Cifor, I ran the harvesting on demand at 15:39 EDT, and this is what is in the db: 3950 | 0 | 6 | 2023-10-16 15:39:37.643 | 0 | 0 | 2023-10-16 15:39:32. 958 | and what is displayed in the UI:

That is, it appears we store (without a timezone) as the local time and then the display also that.

So I'm thinking we DON'T treat any of these as UTC.

Let's discuss more at tech hours.

[scolapasta]

Another data point to see hwo API is working (also need to see if we have an API for harvesting clients above).

Added a dataverse, what I'm getting from database is: 7465821 | Dataverse | 2023-10-16 16:18:50.377 | 2023-10-16 16:18:50.462 | 2023-10-16 16:18:50.393 | 2023-10-16 16:18:50.507 | 2023-10-16 16:18:50.393

API returns UTC for sure: {"status":"OK","data":{"id":7465821,"alias":"messi","name":"GPD test - will delete asap","affiliation":"Dataverse.org","dataverseContacts":[{"displayOrder":0,"contactEmail":"[email protected]"}],"permissionRoot":true,"dataverseType":"TEACHING_COURSES","ownerId":1,"creationDate":"2023-10-16T20:18:50Z"}

[scolapasta]

Another data point, harvesting data API, returns not UTC:

curl "http://localhost:8080/api/harvest/clients/cifor"

{"status":"OK","data":{"nickName":"cifor","dataverseAlias":"cifor_harvested","type":"oai","style":"dataverse","harvestUrl":"https://data.cifor.org/oai","archiveUrl":"https://data.cifor.org/","archiveDescription":"This Dataset is harvested from our partners. Clicking the link will take you directly to the archival source of the data.","metadataFormat":"dataverse_json","set":"cifor_general","schedule":"Weekly, Sat 5 AM ","status":"inActive","lastHarvest":"Mon Oct 16 15:39:32 EDT 2023","lastResult":"SUCCESS","lastSuccessful":"Mon Oct 16 15:39:32 EDT 2023","lastNonEmpty":"Sun Nov 06 05:00:00 EST 2022","lastDatasetsHarvested":0,"lastDatasetsDeleted":4,"lastDatasetsFailed":119}}

[scolapasta]

Looking at JsonPrinter for harvesting all we do is toString(): add("lastHarvest", harvestingClient.getLastHarvestTime() == null ? null : harvestingClient.getLastHarvestTime().toString()).

for dataveres createdate, we call a util method: bld.add("creationDate", Util.getDateTimeFormat().format(dv.getCreateDate()));

and the Util method there:

     public static SimpleDateFormat getDateTimeFormat() {
        return DATETIME_FORMAT_TL.get();
    }

and:

        @Override
        protected SimpleDateFormat initialValue()
        {
            SimpleDateFormat format =  new SimpleDateFormat(DATE_TIME_FORMAT_STRING);
            format.setTimeZone(TimeZone.getTimeZone("UTC"));
            return format;
        }
    };```

[pdurbin]

As discussed in Slack, I moved this to "Waiting" status.

A decision needs to be made about what to do next, if anything.

[cmbz]

2024/03/13

• Addressing this issue would be valuable but is not a priority at this time.
• Move back to the backlog