[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":"dataverseadmin@iq.harvard.edu"}],"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