Display the locally inserted time stamp everywhere

Question

At 6:00am Sydney time, Rory Allan clicks the foofoo button in their browser.

This inserts into the foofoo table with an 8:00pm UTC timestamp in the database of my server.

A lot of people are excited about this foofoo button, and they wanted to see what time of the day Sydney time Rory clicked it.

As I stare at my screen in California and view the status of the click, I want to see that he clicked it at 6:00am, not 1:00pm, which is what I will see if I pull the UTC time from the server and let my browser convert it.

You see, a lot of different people click foofoo, such as Phillip Herman in Germany, or Ivan Efimiov in Russia. And all we care about is the relative time of the day they clicked it, in the location they clicked it, regardless of the viewing location.

---

I don't know the best way to do this. Do I take the local timestamp and convert it to a string, storing it in addition to the real UTC timestamp? Or is this a common problem with a common resolution that I haven't found? I'm guessing / hoping the latter.

This isn't Language specific. These dates have a long journey:

Unix timestamp > Python date > JSON > Node > Mongo > Node > Browser

Answer 1

tl;dr

Exchange date-time values in UTC as strings in standard ISO 8601 format.

Work in UTC

General rule in date-time handling is to think in UTC, work in UTC, log in UTC, share in UTC, and store in UTC. But present in zoned time for the user.

By think in UTC, I mean every programmer needs to learn to stop their parochial thinking about their own particular home time zone. Translating back-and-forth to your own zone to UTC to other zones will drive a person nuts. Think of UTC as The One True Time®. All other zones and offsets are mere variations.

This strategy is much like internationalization. The programmer uses key strings in her own human language to look up string values from the localization tool to present a value (piece of text) in the human language preferred by her user. In date-time handling, the programmer works in UTC but applies a zone preferred by her user for presenting text in the user-interface.

Moment in UTC

The basic Java class for this Instant. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

So when Rory in Sydney clicks his button, we record an Instant object.

Instant instant = Instant.now() ;

instant.toString(): 2017-06-01T09:24:54.435Z

Zoned

To present that moment to Rory in his own time zone, we apply a ZoneId to get a ZonedDateTime object.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "Australia/Sydney" ) ;
ZonedDateTime zdt = instant.atZone( z );

zdt.toString(): 2017-06-01T19:24:54.435+10:00[Australia/Sydney]

Now we have two objects, instant & zdt, that both refer to the same simultaneous moment on the timeline. The only difference is wall-clock time.

If the user in California wants to see the moment of Rory's button click according to Sydney time, then we already have that solution seen above. If not, if the California user wants to see the moment of Rory's button click in her own California clock, then read on.

We can adjust into yet another region’s wall-clock time by applying another time zone.

ZoneId zAmericaLosAngeles = ZoneId.of( "America/Los_Angeles" );
ZonedDateTime zdtAmericaLosAngeles = instant.atZone( zAmericaLosAngeles );

zdtAmericaLosAngeles.toString(): 2017-06-01T02:24:54.435-07:00[America/Los_Angeles]

Now we have three objects that all represent the same simultaneous moment: instant, zdt, and zdtAmericaLosAngeles. One moment, three wall-clock times.

See this above code run live at IdeOne.com.

Time-of-day

If you literally meant you want the time-of-day only, without the date, you can extract a LocalTime object from those objects above.

But think twice about doing this, as presenting a time-only without date and zone can lead to ambiguity and misunderstanding.

LocalTime lt = zdt.toLocalTime();

all we care about is the relative time of the day they clicked it, in the location they clicked it, regardless of the viewing location

If you are really really really sure that is what you want, then combine my advice above. (But I doubt this is a wise way to go.)

LocalTime lt = LocalTime.now( ZoneId.of( "Australia/Sydney" ) ) ;  // Current time-of-day in Sydney.

Library for date-time

We have been using the modern java.time classes in examples above. They are exceptional – I mean than literally. They are virtually unique. Virtually all other platforms have terrible support for date-time work. The predecessor to java.time was the Joda-Time project which was ported to .Net platform as Noda Time. Other than java.time & Noda Time, I know of no other decent library on other platforms.

ISO 8601

The ISO 8601 standard defines many sensible practical formats for textual representation of date-time values.

The java.time classes use the standard formats by default when generating & parsing strings. You have been viewing those ISO 8601 formats in examples above. Except for ZonedDateTime which wisely extends the standard by appending the name of the time zone in square brackets.

The T in the middle separates the date portion from the time-of-day portion.

For UTC, the Z on the end is short for Zulu and means UTC.

For offset-from-UTC, you see a plus/minus number of hours and minutes ahead of or behind UTC. A time zone is history of past, present, and future offsets in use by a particular region.

Database

How databases handle date-times varies widely, though poor support is most common. A database driver, such as JDBC drivers, add another layer of behavior. So no way to succinctly address that here. And this topic is already asked and answered in many other pages in Stack Overflow.

If your database lacks serious date-time support, you may be better off storing the ISO 8601 strings with the Z on the end. These values when sorted alphabetical are also in chronological order.

Answer 2

Ok, clearly 8:00pm UTC is not enough information. But what do you really want to know?

• Is 06:00 enough information?

  • Look for time-only data types, preferably those that reflect time-of-day, rather than elapsed-time. These are often called LocalTime, TimeOfDay, etc.
  • Or, use strings in HH:MM format (on a 24-hour clock)
  • Or record the total number of minutes as an integer (60 * HH) + MM.
  • Or if you need a higher precision, then use seconds, or milliseconds, or microseconds, etc...

• Is 06:00 Australia/Sydney enough information?

  • Store the time as mentioned earlier and store the time zone name in a separate string.
  • But be careful, because without a date you don't know if UTC+10 or UTC+11 was in effect.

• Is 06:00+10:00 enough information?

  • You could look for a time with time zone data type, such as the one that exists in PostgreSQL, though even the PostgreSQL docs strongly discourage using this type.
  • Instead, store the time and offset as separate components.
  • Be careful, because you'd not necessarily know if this data was from Sydney. There are other time zones with UTC+10 at parts of the year too.

Assuming you have the date, such as today, then:

• Is 2017-05-31T06:00 enough information?

  • Store the date and time together in a component that is sometimes called DateTime or LocalDateTime in various languages - but be careful that it is not bound to any specific time zone. Use DateTimeKind.Unspecified in .NET, or "naive" DateTime's in Python, etc.
  • For MongoDB and others that don't have such a type, use a string in ISO8601 format.
  • Be careful, because this time could come from anywhere in the world. Nothing here relates it to a specific point in time.

• Is 2017-05-31T06:00+10:00 enough information?

  • Some languages and databases have a DateTimeOffset or OffsetDateTime type for this purpose.
  • If not, you can store a "Unix Timestamp" and also store the offset from UTC separately.
  • Or you can just store a string in ISO8601 format with the offset included - just be very careful with regard to comparisons / sorting.

• Is 2017-05-31T06:00 Australia/Sydney enough information?

  • Store separately the date+time component from the time zone.
  • Consider that a time may be ambiguous during a DST fall-back transition.

• Is 2017-05-31T06:00+10:00 Australia/Sydney enough information?

  • Here we have everything we might possibly need. Use a ZonedDateTime in Java/Joda-Time/Noda-Time, or an "aware" DateTime in Python (pytz, dateutils, etc.), or similar types when they exist in your platform.
  • Watch out for timestamp with time zone, as you might expect it to store the time zone and it typically does not (despite the name).
  • If not available, then consider storing a Unix timestamp and separate the time zone name as a string.

As you can tell - there are a LOT of options, and it really depends on exact use case and features available in each language/platform. You'll find more details if you search/ask for each one separately.