Consistent date formats

[mkungla]

Describe the bug

Some endpoints return valid RFC3339/ISO8601 date and some not which makes it inconsistent to unmarshal json response.

To Reproduce

query /genesis "systemstart": "2017-09-23T21:44:51Z", has valid RFC3339/ISO8601 time layout while querying some other endpoints e.g. /epoch_info returns "start_time": "2022-02-09T21:44:51", (missing Z for Zero timezone offset) which is not valid RFC3339 layout.

Expected behavior

all date strings to have standard valid layout e.g. with "Z" or tz-numoffset

[rdlrt]

Missed to check this before... Background:

To avoid instance providers [not] modifying the timezone setting in Postgres, which would result in inconsistency of returned data, cardano-db-sync makes use of timestamp without time zone in various tables, to return time in UTC (as DB/API itself should not go beyond UTC for blockchain data). The difference for /genesis exists predominantly due to the way it's added to database (essentially, genesis block for cardano is built from a configuration file, which contains systemStart field).

Now for consistency, the easy method would be to align this entry in genesis table itself - this should sort the results returned from Postgres adhered to without time zone.

However, to support RFC3339/ISO8601 itself:

1. It will require us to change all timestamp without time zone field to timestamp with time zone, and set TZ as UTC.
2. PostgREST will simply pass the initial timezone from postgresql.conf => TimeZone setting, which is difficult to enforce to future instance providers. Absence of this will cause inconsistent tz-numoffset between instances.
3. If we forcibly concatenate "Z" to the RPC using to_char , the date type/RPC of timestamp returned from PostgREST will change to char instead.
4. Doing a cast here with timezone to any endpoints will have similar effect as points 1 and 2.

Given these considerations, I'd find it difficult to expect every instance provider to align their system and postgres time zones. Instead, it'd be interesting to get a view of what folks think is a better solution:

1. Use cast to char for data type on every endpoint that shows timestamp
2. Only rectify the existing genesis data returned.
3. Try to pursue moving their system + postgres timestampts to UTC amongst all instance providers, followed by changing timestamp without time zone data types for tables/RPCs/procedures in grest schema.
4. cast end timestamp to UNIX date time

[mkungla]

Reopened by discussion on telegram

[mkungla]

@rdlrt so perhaps it would make sense to have now standard unix timestamp in seconds (integer) and revisit that when that day comes when network implements higher precision? e.g. milliseconds, or nanosecond. One way or other exposing floats from API is likely to cause more problems than have benefits compared to string timestamps