Building a datetime picker for a database

Xata, our modern database, supports datetime columns.

When we launched last year we decided to just use the native browser date picker. This date picker has a number of issues, and we just weren’t happy with it.

In Xata, datetimes returned from our API look like this: 2000-01-01T01:02:03.123Z

Lets break down all the parts:

     month hour  second
      ∨∨    ∨∨    ∨∨
"2000-01-01T01:02:03.123Z"
 ∧∧∧∧    ∧∧    ∧∧    ∧∧∧
 year   day   minute millisecond

All of Xata’s datetimes are saved and returned with the UTC timezone.

Xata has two main personas:

• Developers
• Low-code users

At it’s core, Xata is a database aimed at developers.

Developers are power users: They’re comfortable with not leaving their keyboard, they understand datetime strings, and, most of all, they love and respect the UTC timezone.

Entrepreneurs, hackers, customer success, you name it.

They use Xata’s Web UI to quickly view their data, add records and make edits.

They might be more likely to prefer using a date picker widget with the mouse or paste a value in from google sheets or airtable.

There are a few scenarios when a user selects a datetime inside our WebUI:

This might be with a precision of:

• Day: e.g. 2000-01-01
• Time: e.g. 2000-01-01T01:02:03
• Millisecond: e.g. 2000-01-01T01:02:03.123

They’re pasting a datetime from another source

• e.g. 25/01/2000 pasted from Excel
• e.g. 2nd Mar 2015 23:22:00 pasted from another system.

• If they’re testing something or adding dummy data

• Next wednesday at 3pm

Here is the new date timer picker Dave Snider and I built. In glorious UTC.

The datetimes in Xata are shown inside an <input />. As soon as the input is focused, we pop up the picker.

We close the the picker when focus moves away from the picker and input.

Selecting a date in our datetime picker is easy and straightforward. Users can jump back months or select the month and year from the dropdowns.

Notice that when a user selects the date, the value of the input changes to the new date.

To set the time, we have built our own component with one input each for hour, minute, second, and millisecond.

As the user completes each input, focus automatically shifts to the next one.

Here the user is typing: 1 2 3 4 5 6 7 8 9

When a user focuses on an input, all of the text in the input is selected. This means that as soon as they start typing, they will overwrite the existing value in the input.

These inputs have strict validation, which makes it impossible to enter an invalid time. Leaving an input empty sets it to 00.

Users can navigate between inputs using the tab key as expected.

As the user enters their time, the value of the input with the datetime updates as well.

If users prefer, they can use the main input to edit their datetime directly.

As they type text the datetime picker components update to show the correct values - it’s two-way connected.

Typing directly into the input is good for a couple of reasons:

• It’s quick, users can use their keyboard to make a quick edit and press enter or tab to save it
• It allows us to support more formats…

When entering a datetime, it's possible it’s come from another data source. That data source likely uses a different format to Xata.

Our date picker supports nearly all common date formats. Here are some examples you can paste or type in:

• 15/5/2000
• 15.05.2000 14:30
• may 15th 2000

This is great if you are copying data from Google Sheets or Excel and pasting it into Xata.

We also support human readable formats:

• tomorrow at 4pm
• next wednesday at 15:00
• now

When you type the custom format you’ll see the date and time update automatically. When the date picker loses focus the input displays the UTC time again: 2000-05-15T14:00:00.000Z

The value the user sees inside the main input is exactly what gets saved inside Xata.

It could be hard for users to discover this feature. To help with this we’ve added a couple of buttons at the top of the picker to help the user quickly try it out with a couple of examples.

There are several off-the-shelf date pickers we could have used, but we chose to use the excellent dayzed library, which provides helpful React hooks for building a calendar widget. At Xata, we use Chakra as our component library, so it was quick and easy to build the rest of the calendar using it.

We couldn't find an off-the-shelf time picker that worked the way we wanted. We wanted users to be able to quickly edit just one component of the time, which is not a common capability among many time pickers. Additionally, our requirement for milliseconds is somewhat niche.

The time picker uses regular expressions to evaluate whether a partially or fully entered value is valid.

Here is our code to validate hours:

const HOUR_REGEX = /^([0-1][0-9]|2[0-3])$/;
const ANY_DIGITS_REGEX = /^(\d)+$/;

export const isValidHour = (hours: string) => {
  return HOUR_REGEX.test(hours);
};

export const isValidPartialHour = (hours: string) => {
  if (hours === '') {
    return true;
  }
  if (hours.length === 1) {
    return ANY_DIGITS_REGEX.test(hours);
  }
  return isValidHour(hours);
};

Here is the code that decides whether to update the value and moves the focus to the next input if the cursor is at the end of the input:

<Input
  onChange={(e) => {
    const newValue = e.target.value;
    const cursorIndex = e.target.selectionStart ?? 0;
    if (isValidPartialHour(newValue)) {
      onChange(newValue);
      if (newValue.length === 2 && cursorIndex === 2) {
        jumpToNextInput();
      }
    }
  }}
/>

We use Chrono, which makes it easy to parse almost any datetime.

import * as chrono from 'chrono-node';

chrono.parseDate('tomorrow at 3pm');
// => Fri March 30 2023 15:00:00
chrono.parseDate('Fri March 30 2023 15:00:00');
// => Fri March 30 2023 15:00:00

One modification we made to the default Chrono behaviour is to throw away any time information that is implied and only keep known values:

chrono.parse('tomorrow')[0].start;
// => {
//  date: '2023-03-24T01:02:03.123Z,
//	knownValues: {day: 24, month: 3: year 2023},
//  impliedValues: {hour:1 , minute:2, second: 3, millisecond: 123}
// }

In this example, the user said "tomorrow" without specifying any time information. Therefore, we discard the implied values and only use the known values, producing: March 24th, 2023 at 00:00:00.000 UTC.

One difficulty throughout this project is that timezones are hard. There is no timezone silver bullet, but here’s some advice:

• Unit test!
  • Run your unit tests manually with TZ="Europe/Berlin" pnpm test to verify they still pass.
• Manually test by setting your system clock to a different timezone.
• Test daylight savings by setting dates in your picker that are after the clocks change.

After shipping our v2 DateTime picker, our next goal is to allow users to save their preferred DateTime format and timezone.

For example:

Users can change their preferences in our web UI to display all datetimes in the format 29.05.2023 3pm, presented in their local timezone instead of UTC.

To do this we need to figure out how to allow users edit datetimes in the picker which currently assumes everything is in UTC or restrict users to editing datetimes in UTC.