How Should I Store/Display My Date/Time Field?

Michael Vasilevsky
Michael Vasilevsky used Ask the Experts™
on
I know there's a ton of questions around date/time storage and formatting, but I'm struggling to get the right approach. My app is a Node.js app with Express using mongoose with a MongoDB backend. I'm using moment.js to format my date/times.

The schema in question looks like:

const stateSchema = new mongoose.Schema({
  changed: {
    type: Date,
    default: new Date()
  }
});

Open in new window

Based on the docs, I was expecting the date to be stored in UTC by default, but instead I see this:
changed: 2018-08-15 17:26:14.499

Open in new window

I'm displaying the date/time with something like (pug):

p= `Changed: ${moment(state.changed).format('MMMM Do YYYY, h:mm:ss a')}`

Open in new window

On localhost, the app shows "August 15th 2018, 5:26:14 pm" but on the hosted Heroku app I get "August 15th 2018, 10:26:14 pm." I am expecting "August 15th 2018, 5:26:14 pm" because I'm opening the Heroku app in the same timezone, but it looks like it's giving me the server timezone.

What should I do to make sure the date/time shows up in the client's local timezone?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
How a date is stored in a database is one thing.  How it is displayed is another.  The display of dates is normally controlled by the user's Windows settings.  If you store a date as UTC, you would have to convert it for display by determining the user computer's time zone and whether or not they used DST.
Michael VasilevskySolutions Architect

Author

Commented:
Maybe my question then boils down to how to determine the client's timezone and convert the UTC date in a Node.js app?
Distinguished Expert 2017

Commented:
If I knew how to do that for your platform, I would have included the code.  This question came to me because I answer SQL questions but I develop using MS Access.  So, I understand the problem but cannot provide any code.  Sorry.
Michael VasilevskySolutions Architect

Author

Commented:
I think this is the answer. It gives me the wrong time on localhost, but should be the correct time on the hosted app, no matter where I am.

Add offset to schema:

    const stateSchema = new mongoose.Schema({
      changed: {
        type: Date,
        default: new Date()
      },
      offset: {
        type: Number,
        default: new Date().getTimezoneOffset()
      }
    });

Open in new window


Then subtract the offset when displaying:

    p= `Changed: ${h.moment(state.changed.getTime() - (state.offset * 60000)).format('MMMM Do YYYY, h:mm:ss a')}`

Open in new window

Solutions Architect
Commented:
See above

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial