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.

Datetimes in Xata

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.

Who uses our date picker?

Xata has two main personas:

• Developers
• Low-code users

Developers

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.

Low-code users

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.

Use cases

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

User has a specific date they’d like to set.

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

Pasting

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.

Any date is fine

• If they’re testing something or adding dummy data

User has a relative or human time in mind

• Next wednesday at 3pm

Our new date time picker

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

Opening the date picker

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.

Setting the date

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.

Setting the time

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.

Typing in the input

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…

Supporting 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.

How we built it

The Date picker

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.

The Time picker

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();
      }
    }
  }}
/>

Parsing any datetime string

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.

Timezones (are hard!)

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.

What’s next?

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.