CODEWITHSUNDEEP
mysqlreactjsfunctiondatedatetime
Bill
Bill

Reputation: 33

Creating a booking system, but I struggle with inserting the input date to MySQL table

I am new to programming trying to create a rental system for cars with reactjs, node and mysql, but I struggle with inserting date to the database. I know I have to make it a string somehow and also make it local timezone, but can't figure it out.

date.js: 

dateOut = "";
dateIn = "";
location = "";
render()
  return(
}
<input type="date" value={this.dateOut} onChange={e => (this.dateOut = e.target.value)} />
<input type="date" value={this.dateIn} onChange={e => (this.dateIn = e.target.value)} />
<input type="text" value={this.location} onChange={e => (this.location = e.target.value)}>
);

add() {
    orderService.addOrder(
      this.dateOut,
      this.dateIn,
      this.location
      id => {
        history.push('/cars/' + id);
      }
    );
  }
} ```

services.js:

  ```addOrder(dateOut, dateIn, location, success) {
    connection.query(
      'insert into Orders (dateOut, dateIn, location, success) values (?, ?, ?)',
      [dateOut, dateIn, location],
      (error, results) => {
        if (error) return console.error(error);

        success(results.insertId);
      }
    );
  } ```

Upvotes: 2

Views: 281

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Here's how to handle this.

  1. Declare your dateOut and dateIn columns as TIMESTAMP data types in your table.
  2. When you provide date/time values to your insert queries, use text strings with formats like 2019-04-08 16:52. Or use Javascript Date objects.
  3. When registering your users, ask for their timezone preference, specifically asking them to choose from one of the values in mysql.time_zone_name.name.
  4. Store that preference, as a string (like Asia/Kolkata or America/Halifax) in your user preference table. VARCHAR(63) is a good choice of data type for that column.
  5. Each time you use a MySQL connection in your app on behalf of a user, first issue the command SET time_zone = whateverTheyChose to set the user's time zone preference.

Why is this good? TIMESTAMP data type values are always stored in UTC, and always translated to/from the currently set time zone when retrieving or storing them.

If you want to insert the present date/time value into a column, you can do something like this:

insert into Orders 
       (dateOut, dateIn, location, success) 
values (NOW(),    NOW(),        ?,       ?)

Notice how the values and placeholders in the values() clause correspond one-to-one with the items in the list of columns.

You can read about MySQL's time zone handling system on the 'toobz. It's based on IANA's zoneinfo database. The maintainers of that database take care of all the strange temporopolitical considerations like when daylight time begins and ends in Turkmenistan or Indiana USA, so the rest of us don't have to.

If you use DATE or DATETIME data types rather than TIMESTAMP, you don't get that automatic time zone stuff. Those data types are, more or less, like photographs of clocks.

No matter which datatype you use, you can use the MySQL function called TIMESTAMPDIFF() to compute durations. For example,

TIMESTAMPDIFF(HOUR, dateOut, dateIn)

tells you the number of hours between your two date/time values. It will work correctly even if your two values span the daylight time switchover days.

It's worth a lot of your time to learn about how your dbms handles dates and times. They do it very well so you don't have to reinvent the flat tire.

And, it's worth your trouble to set up all this correctly very early in the life of your application. It's very very hard to change a running system to handle these time zones. (Don't ask how I know it's hard!)

Upvotes: 1

Related Questions