Add Date / DateTime / Timestamp Fields

[keystonejs]

Add Date / DateTime / Timestamp Fields

[molomby]

Good chats about this in the office this morning. The consensus here is pretty close to what's suggested but..

• We're not sure there's a need for the JS-Date-like Epoch type
• We'd probably name them differently

So, the counter suggestion is basically:

• CalendarDay (what @JedWatson's called Date) -- the abstract idea of a day of the year, like a birthday or national holiday. Probably stored as a short ISO string (YYYY-MM-DD or YYMMDD would also work; just gotta pick one).
• DateTimeTz (what @JedWatson's called DateTime) -- A full ISO-style value with date, time and time zone. Encoded using native data types if they exist (eg. pgSQL's timestamp with time zone) or, as a fallback, an ISO string. This means a lot of the time (in JS, JSON, GraphQL) these end up being passed around a strings, which is fine (tools like GraphQL, knex, etc. already do this).

A few things needing further discussion:

There may still be value in having a JS-Date-like Epoch type that encodes milliseconds from 1970 in UTC. It seems weird to build a JS framework that doesn't really deal with native JS dates. On the other hand, they are pretty useless, so better to lean towards best-practice.

Also, one issue I've run up against is that of precision. Most DBs will let you store date times with greater than millisecond precision, which is can both be legitimately helpful and cause significant issues. It's not really possible to deal with these easily in JS-land without dropping precision (not even in moment) which can complicate equality checks. In my work I've been reducing the precision of my columns to JS-levels to avoid these issues (ie. timestamp(3) with time zone rather than the default timestamp(6) with time zone in pgSQL).

@timleslie @jesstelford @dominikwilkowski -- Have I missed/misrepresented anything?

[molomby]

@dominikwilkowski, re: Precision values -- hrtime and peformance.now() give you high precision measurements but aren't any help when it comes to manipulating them. Eg, let say you pull two high precision date times with time zones from your DB, encoded as ISO strings:

var a = "2018-05-15T10:00:30.000123+1000";
var b = "2018-05-15T09:00:30.000234+0900";

How do you determine their order? If you convert to JS Dates they produce the same Epoch value (1526342430000) so look equal. If you compare them as strings a looks bigger because of the time zone. In reality b is the larger/later value.

There's no good way that I know of to work with these values; the language just doesn't support them (it and will silently modify values, pushing them slightly backwards in time). There are libraries that might help (eg. timestamp-nano?) but a lot of these are just a higher resolution Epoch values so you're still hosed when it comes to, for eg, dates before 1970.

These seem like edge cases but they seem to come up surprisingly often. Unless the dev has a decent understanding of the underlying encodings the issues they create can be pretty gnarly to debug.

[molomby]

Further to this ^^ at some point we'll probably want an Offset (or TimeZoneOffset?) field type too. If we get the format of these right (ie. use the relevant parts of the ISO format) we could end up with the delightful property of being able to concatenate the different parts directly into the full ISO format.

That is to say:

• CalendarDay should store YYYY-MM-DD (as it currently does)
• TimeOfDay should store HH:mm:ss.nnn (as suggested above) and..
• TimeZoneOffset should store ±hh:mm (eg. '+10:00', -03:00, etc.) or the literal 'Z'

Then.. ${day}T${time}${offset} would be a full ISO 8601 string. How wonderful. ✨

[molomby]

We can probably close this. It's either been implemented or superseded by more recent issues like #2937 and #2938.

[JedWatson]

p.s this could be a good choice for the date picker, with some style tweaks by @jossmac - http://react-day-picker.js.org/examples/input-date-fns

[JedWatson]

There's also an open question as to whether we should support a field that stores a date + time without any timezone information. There are some situations where this is useful, because you want to represent "2pm Thursday 17th May" without any opinion about where in the world that is happening. But I've left that off the list until we have a clear use-case for it.

[jesstelford]

ACK. Discussions ongoing.

[dominikwilkowski]

When it comes to precision measurements I prefer to use hrtime in node and performance.now() in the browser (though the later one is affected by spectra timing attacks and has been rounded in current browsers. I suspect they will be fixed again soon.)
Maybe this is helpful for this discussion.

PS: I suggest to stick with YYYY-MM-DD as that is a pretty common format and avoids typos like YYMMDD.

[jesstelford]

Possible tool for the View portions: https://github.com/geeofree/kalendaryo

[timleslie]

I am going to implement a DateTime field type. It will support a date + time + offset, with the time having millisecond precision. I will reference back to this issue when I have a PR.

[molomby]

Is there any appetite for a Time or TimeOfDay field type? This has come up a lot in Hotel101 where rate cards are defined in part by the times of day people are expected to arrive after and leave before.

We've been storing the number of minutes since midnight but this doesn't model the intention accurately. When the user says "5 AM", they mean "5 AM", not "300 minutes past midnight". This becomes relevant around daylight saving transitions. Eg. midnight on the Oct 7 this year, plus 300 minutes is 6 AM.

var moment = require("moment-timezone");
var offsetMins = 300;
var midnight = moment("2018-10-07").tz('Australia/Sydney');
var startTime = moment(midnight).add(offsetMins, 'minutes');

startTime.format('YYYY-MM-DD [at] H:mm a');
// "2018-10-07 at 6:00 am" (incorrect)

It would be nice to capture the intent of "this time of day, every day, regardless of time zone or offset" in a type. I imagine we'd store it as per the time part of an ISO 8601 string (ie. HH:mm:ss.nnn) with millisecond precision.

[mikehazell]

The current implementation of DateTime is a pain when upgrading from K4. It requires a migration which:
a) renames the field from eg. 'createdAt' to 'createdAt_utc' and
b) adds a 'createdAt_offset'.

The offset here is really bad. As I have no way of knowing what the offset should have been, if I now set some default value it is more than likely incorrect.

[jesstelford]

@mikehazell Are you saying the previous createdAt did not store a timezone / offset? If so, there's no way to recover that information regardless of migrating to KS5 or not.

But if you do have the timezone/offset, @molomby mentioned above you can suffix that into a ISO 8601 string in place of the 'Z':

new Date("2018-10-25T21:20:02+10:00")
// gives: Date 2018-10-25T11:20:02.000Z

Which gives you a nice migration path; simply store the datetime string as an ISO 8601 with the correct offset suffixed, and javascript will convert that to UTC based on the host machine's locale.

Although, storing the dates is a little harder (because JS's implementation of .toISOString() will always output the UTC offset.

[mikehazell]

@jesstelford the previous createdAt is an ISOString. No offset or timezone info available.
I don't think I need an offset for this field either.

Correct me if I'm wrong here. As I understand it:

• The DateTime field type is the only option currently available to me.
• I will have to run a migration that maps what was previously just a Date in a single field to 2 new seperate fields as below.

// K4 Date field
"createdAt" : ISODate("2018-10-25T06:02:00.000Z"),

// K5 DateTime field
"createdAt_offset" : "+0:00",
"createdAt_utc" : ISODate("2018-10-25T06:02:00.000Z")

I guess this is just unexpected and seems flawed from a upgrade path point of view. It suggests every keystone site that uses dates will need to migrate every date field in every list, including the tracking fields createdAt and updatedAt. Am I missing something or is this still work in progress?

[jesstelford]

My apollogies - I missread your earlier comment. I see now you're talking about data migration, not just application level migration.

It looks like there could be room for a DateTime_KS4 field which retains compatability, and eases the upgrade path to KS5.

[stale]

It looks like there hasn't been any activity here in over 6 months. Sorry about that! We've flagged this issue for special attention. It wil be manually reviewed by maintainers, not automatically closed. If you have any additional information please leave us a comment. It really helps! Thank you for you contribution. :)

[molomby]

The Temporal proposal is relevant to this. We'll probably revisit some of our field types as it become standard.

Date has been a long-standing pain point in ECMAScript. This is a proposal for Temporal, a global Object that acts as a top-level namespace [..], that brings a modern date/time API to the ECMAScript language.