如何从 javascript 到 mysql 读取和存储日期时间

2022-01-19 00:00:00 datetime mysql momentjs sequelize.js

我正在构建一个应用程序 node.js，用户在其中创建一个记录，其中包含一个名为游览日期和时间的字段.我正在使用 daterangepicker 插件来显示日期和时间下拉窗口.一旦用户保存记录，它就会作为日期时间字段保存在 MySQL 表中.

I am building an application node.js where a user creates a record with a field named tour date and time. I am using daterangepicker plugin to show the date and time dropdown window. Once the user saves the record it is saved in the MySQL table as datetime field.

然后我使用 fullcalendar 来显示给定月份-年份的所有旅行日期.

Then I am using fullcalendar to display all the tour dates for a given month-year.

但这就是问题所在.假设我选择 28/11/2018 下午 6:30 作为游览日期和时间.在日期范围选择器中，它被保存为

But here is the problem. Lets say I select 28/11/2018 6:30 PM as the tour date and time. In the daterange picker it gets saved as

2018-11-28 18:30

2018-11-28 18:30

$('input#tour_date').daterangepicker({
    singleDatePicker: true,
    showDropdowns: true,
    timePicker: true,
    timePickerIncrement: 30,
    locale: {
        format: 'DD/MM/YYYY hh:mm A'
    }
}, function (chosendate) {
    $('input[name="tour_date"]').val(moment(chosendate).format('YYYY-MM-DD hh:mm'));
    //the value for input becomes 2018-11-28 18:30
});

我没有为 daterangepicker 或 momentjs 设置任何时区.我从表单中获得的日期时间(在保存到数据库之前)是相同的.即它在 2018-11-28 18:30 出现.

I have not set any timezone for daterangepicker or momentjs. The datetime I get from the form (before saving to the database) is the same. i.e. it comes in as 2018-11-28 18:30.

但是在保存到数据库时，它会保存为

However on saving to the database, it gets saved as 

2018-11-28 01:00:00

它将 +06:30(IST，印度标准时间值)添加到传入的日期时间字段.

It adds +06:30 (IST, Indian Standard Time value) to the incoming datetime field.

因此，当我打开日历时，日期正确(28/11/2018)但时间不正确.用户保存了期望时间为下午 6.30 的字段，但看到的是 01:00

So when I open my calendar, the date is correct (28/11/2018) but the time is incorrect. The user saved the field expecting the time to be 6.30 PM but instead sees 01:00

我将日期时间保存为

var tourDate = new Date(req.body.tour_date).toISOString();

我正在使用 Sequelize ORM，但我没有给出任何特定的时区.

I am using Sequelize ORM and I have not given any specific timezone. 

const sequelize = new Sequelize(process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASSWORD, {
    host: process.env.DB_HOST,
    port: 3306,
    dialect: 'mysql',
    pool: {
        max: 10,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    logging: false
});

我想要做的是无论用户在哪个时区.我想将给定的日期和时间按原样保存在数据库中并按原样检索它，以显示在日历中.

What I want to do is no matter which timezone the user is present in. I want to save the given date and time as is in the database and retrieve it as is, to be shown in the calendar.

我如何做到这一点?

推荐答案

如果你是通过sequelize存储日期，那么它会将日期存储为UTC，

所以从前端只需传递 UTC 中的日期，这样它就会被存储如果您使用日期时间选择器，它会自动将 UTC 转换为您的时区，只需将 UTC 数据传递给您的插件.

So from the front end just pass the date in UTC , so it will get stored and if you are using date time picker it will automatically convert the UTC into your timezone , just pass the UTC data to your plugin.

通过时只需使用 UTC 即可.

相关文章