有人知道如何将JS日期时间转换为MySQL日期时间吗?此外,是否有一种方法可以向JS日期时间添加特定数量的分钟,然后将其传递给MySQL日期时间?
有人知道如何将JS日期时间转换为MySQL日期时间吗?此外,是否有一种方法可以向JS日期时间添加特定数量的分钟,然后将其传递给MySQL日期时间?
简短版本:
// JavaScript timestamps need to be converted to UTC time to match MySQL
// MySQL formatted UTC timestamp +30 minutes
let d = new Date()
let mySqlTimestamp = new Date(
d.getFullYear(),
d.getMonth(),
d.getDate(),
d.getHours(),
(d.getMinutes() + 30), // add 30 minutes
d.getSeconds(),
d.getMilliseconds()
).toISOString().slice(0, 19).replace('T', ' ')
console.log("MySQL formatted UTC timestamp: " + mySqlTimestamp)
在MySQL中存储时间戳,通常最好选择UTC时间。如果您没有root访问权限,则可以在连接开始时运行set time_zone = '+00:00'
。
使用convert_tz方法在MySQL中以特定的时区显示时间戳。
select convert_tz(now(), 'SYSTEM', 'America/Los_Angeles');
JavaScript时间戳基于设备的时钟,并包含时区信息。在发送从JavaScript生成的任何时间戳之前,您应该将它们转换为UTC时间。JavaScript有一个名为toISOString()的方法,用于将JavaScript时间戳格式化为类似于MySQL时间戳的形式,并将时间戳转换为UTC时间。最终的清理工作通过slice和replace实现。
let timestmap = new Date()
timestmap.toISOString().slice(0, 19).replace('T', ' ')
长版本展示正在发生的事情:
// JavaScript timestamps need to be converted to UTC time to match MySQL
// local timezone provided by user's device
let d = new Date()
console.log("JavaScript timestamp: " + d.toLocaleString())
// add 30 minutes
let add30Minutes = new Date(
d.getFullYear(),
d.getMonth(),
d.getDate(),
d.getHours(),
(d.getMinutes() + 30), // add 30 minutes
d.getSeconds(),
d.getMilliseconds()
)
console.log("Add 30 mins: " + add30Minutes.toLocaleString())
// ISO formatted UTC timestamp
// timezone is always zero UTC offset, as denoted by the suffix "Z"
let isoString = add30Minutes.toISOString()
console.log("ISO formatted UTC timestamp: " + isoString)
// MySQL formatted UTC timestamp: YYYY-MM-DD HH:MM:SS
let mySqlTimestamp = isoString.slice(0, 19).replace('T', ' ')
console.log("MySQL formatted UTC timestamp: " + mySqlTimestamp)
使用@gajus的答案概念来完全解决(保持时区):
var d = new Date(),
finalDate = d.toISOString().split('T')[0]+' '+d.toTimeString().split(' ')[0];
console.log(finalDate); //2018-09-28 16:19:34 --example output
var data = new Date($.now()); // without jquery remove this $.now()
console.log(data)// Thu Jun 23 2016 15:48:24 GMT+0530 (IST)
var d = new Date,
dformat = [d.getFullYear() ,d.getMonth()+1,
d.getDate()
].join('-')+' '+
[d.getHours(),
d.getMinutes(),
d.getSeconds()].join(':');
console.log(dformat) //2016-6-23 15:54:16
var date = moment().format('YYYY-MM-DD H:mm:ss');
console.log(date) // 2016-06-23 15:59:08
Using toJSON()
date function as below:
var sqlDatetime = new Date(new Date().getTime() - new Date().getTimezoneOffset() * 60 * 1000).toJSON().slice(0, 19).replace('T', ' ');
console.log(sqlDatetime);
这里使用了@Gayus的解决方案,使用从toISOString()
输出的格式,但它会调整分钟数以考虑时区。
最终格式:2022-03-01 13:32:51
let ts = new Date();
ts.setMinutes(ts.getMinutes() - ts.getTimezoneOffset());
console.log(ts.toISOString().slice(0, 19).replace('T', ' '));
var _t = new Date();
最初的回答
_t.toLocaleString('indian', { timeZone: 'UTC' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');
或者
_t.toISOString().slice(0, 19).replace('T', ' ');
and if want in specific timezone then
_t.toLocaleString('indian', { timeZone: 'asia/kolkata' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');
new Date().toISOString().slice(0, 19).replace("T", " ")
<script>
console.log(getTimestamp("Europe/Amsterdam")); // Europe/Amsterdam
console.log(getTimestamp()); // UTC
function getTimestamp(timezone) {
if (timezone) {
var dateObject = new Date().toLocaleString("nl-NL", { // it will parse with the timeZone element, not this one
timeZone: timezone, // timezone eg "Europe/Amsterdam" or "UTC"
month: "2-digit",
day: "2-digit",
year: "numeric",
hour: "2-digit",
minute: "2-digit",
second: "2-digit",
});
let [dateRaw, timeRaw] = dateObject.split(" ");
let [day, month, year] = dateRaw.split("-");
var timestamp = year + "-" + month + "-" + day + " " + timeRaw;
}else{
// UTC from @Gajus, 95% faster then the above
timestamp = new Date().toISOString().slice(0, 19).replace("T", " ");
}
return timestamp; // YYYY-MM-DD HH:MI:SS
}
</script>
// Current date / time as a timestamp:
let jsTimestamp = Date.now();
// **OR** a specific date / time as a timestamp:
jsTimestamp = new Date("2020-11-17 16:34:59").getTime();
// Adding 30 minutes (to answer the second part of the question):
jsTimestamp += 30 * 1000;
// Example query converting Javascript timestamp into a MySQL date
let sql = 'SELECT FROM_UNIXTIME(' + jsTimestamp + ' / 1000) AS mysql_date_time';