如何从JavaScript到mysql直接读取和存储日期时间 [英] How to read and store datetime as is from javascript to mysql

问题描述

我正在构建一个应用程序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 PM作为旅行日期和时间.在日期范围选择器中，将其另存为

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(印度标准时间的印度标准时间值)添加到传入的日期时间字段中.

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

因此，当我打开日历时，日期是正确的(28/11/2018)，但是时间不正确.用户保存该字段，预计时间为6.30 PM，但看到的是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.

我该如何实现?

推荐答案

如果您通过续集存储日期，则它将日期存储为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，就可以了.

这篇关于如何从JavaScript到mysql直接读取和存储日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！