在 mySQL 中存储 javascript Date()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6044052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing javascript Date() in mySQL
提问by Daniel H
I currently have a javascript variable which records the current date and time like so:
我目前有一个 javascript 变量，它记录当前的日期和时间，如下所示：
var time_of_call;
time_of_call = new Date();
and I need to store it in a MySQL database. When I try to upload it, the column just appears blank but I'm not sure what I'm doing wrong. I know it's not a problem with the mysql query because I have tried entering different values and it works OK.
我需要将它存储在 MySQL 数据库中。当我尝试上传它时，该列只是显示为空白，但我不确定我做错了什么。我知道这不是 mysql 查询的问题，因为我尝试输入不同的值并且它工作正常。
I have the column set to DATETIMEand I am uploading the value unformatted. Could someone please explain what I need to do differently?
我将列设置为DATETIME并且我正在上传未格式化的值。有人可以解释一下我需要做的不同吗？
Thanks for any help
谢谢你的帮助
P.s. I can't use NOW()because I am using that to capture the time that the record is actually captured, and this time_of_call records the time a call actually comes in.
Ps我不能用，NOW()因为我用它来捕获实际捕获记录的时间，而这个 time_of_call 记录了呼叫实际进入的时间。
回答by John Green
In JavaScript, the underlying value of a Date object is in milliseconds, while Unix servers (and MySQL internally) uses whole seconds.
在 JavaScript 中，Date 对象的底层值以毫秒为单位，而 Unix 服务器（以及内部的 MySQL）使用整秒。
To get the underlying value for a javascript date object:
要获取 javascript 日期对象的基础值：
var pDate = new Date();
var pDateSeconds = pDate.valueOf()/1000;
From here, you'll send it to the server... it is up to you whether or not to divide it by 1000, but it has to be done somewhere. From this value, you could just call something like PHP's date('Y-m-d H:i:s', $pDateSeconds); on it.
从这里，你将把它发送到服务器......是否将它除以 1000 取决于你，但它必须在某个地方完成。从这个值，你可以调用类似 PHP 的 date('Ymd H:i:s', $pDateSeconds); 之类的东西。在上面。
Or, you could just use the built-in function in MySQL:
或者，您可以只使用 MySQL 中的内置函数：
$sql = 'UPDATE table_name
SET field_name=FROM_UNIXTIME('.$pDateSeconds.')
WHERE field_name='.$row_id;
回答by Christian
You must convert your format. Also, you don't "upload" an object.
您必须转换格式。此外，您不会“上传”对象。
At least, you have to do: time_of_call.getTime();which returns a timestamp.
至少，您必须这样做：time_of_call.getTime();它返回一个时间戳。
After uploading a timestamp, you have to convert to the DB's native format, eg: date('d-m-Y',(int)$_REQUEST['time_of_call']);
上传时间戳后，您必须转换为数据库的本机格式，例如：date('d-m-Y',(int)$_REQUEST['time_of_call']);
The date format depends on whether you used DATE, DATETIME, TIME, TIMESTAMP or INT.
日期格式取决于您使用的是 DATE、DATETIME、TIME、TIMESTAMP 还是 INT。
If you used either TIMESTAMP or INT (which is best IMHO), you don't need any conversion.
如果您使用 TIMESTAMP 或 INT（恕我直言最好），则不需要任何转换。
Important:A javascript timestamp is in milliseconds, whereas a PHP timestamp is in seconds.
You will have to do time=time_of_call.getTime()/1000;to fix this.
重要提示：javascript 时间戳以毫秒为单位，而 PHP 时间戳以秒为单位。你将不得不time=time_of_call.getTime()/1000;解决这个问题。
回答by RJD22
Afaik Date doesn't add leading zero's to the day and month you should format the date like this:
Afaik Date 不会在日期和月份添加前导零，您应该像这样格式化日期：
yyyy-mm-dd hh:mm:ss
To get the expected result you could use a function:
要获得预期结果，您可以使用一个函数：
Javascript:
Javascript：
function formatDate(date1) {
return date1.getFullYear() + '-' +
(date1.getMonth() < 9 ? '0' : '') + (date1.getMonth()+1) + '-' +
(date1.getDate() < 10 ? '0' : '') + date1.getDate();
}
回答by Ryan Tuosto
You could use either of these to add a method for getting SQL formatted timestamps from a JS Dateobject.
您可以使用其中任何一个来添加从 JSDate对象获取 SQL 格式时间戳的方法。
First in user's local time:
首先在用户的本地时间：
Date.prototype.getTimestamp = function() {
var year = this.getFullYear(),
month = this.getMonth(),
day = this.getDate(),
hours = this.getHours(),
minutes = this.getMinutes(),
seconds = this.getSeconds();
month = month < 10 ? "0" + month : month;
day = day < 10 ? "0" + day : day;
hours = hours < 10 ? "0" + hours : hours;
minutes = minutes < 10 ? "0" + minutes : minutes;
seconds = seconds < 10 ? "0" + seconds : seconds;
return year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds;
}
var d = new Date();
console.log(d.getTimestamp());Second in UTC:
UTC 中的第二个：
Date.prototype.getUTCTimestamp = function() {
var year = this.getUTCFullYear(),
month = this.getUTCMonth(),
day = this.getUTCDate(),
hours = this.getUTCHours(),
minutes = this.getUTCMinutes(),
seconds = this.getUTCSeconds();
month = month < 10 ? "0" + month : month;
day = day < 10 ? "0" + day : day;
hours = hours < 10 ? "0" + hours : hours;
minutes = minutes < 10 ? "0" + minutes : minutes;
seconds = seconds < 10 ? "0" + seconds : seconds;
return year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds;
}
var d = new Date();
console.log(d.getUTCTimestamp());I've created a lightweight script that extends the Date object with these and other commonly needed here: https://github.com/rtuosto/js-date-format
我创建了一个轻量级脚本，它使用这些和其他通常需要的内容扩展 Date 对象：https: //github.com/rtuosto/js-date-format
回答by Neeraj Singh
Try This:
Quick Java Script Date function :-)
/***************************************************
* Function for Fetch Date, Day, Time, Month etc..
* input @param = month, date, time, mysql_date etc..
**************************************************/
function getDateNow(output) {
var dateObj = new Date();
var dateString = dateObj.toString();
dateArray = dateString.split(” “);
if (output == ‘day') {
output = dateArray[0];
} else if (output == ‘month') {
output = dateArray[1];
} else if (output == ‘date') {
output = dateArray[2];
} else if (output == ‘year') {
output = dateArray[3];
} else if (output == ‘time') {
output = dateArray[4];
} else if (output == ‘am_pm') {
output = dateArray[5];
} else if (output == ‘mysql_date') {
var dt = new Date();
output = dt.toYMD();
}else {
output = dateArray[6];
}
return output;
}
/*****************************************************
* Function for Fetch date like MySQL date fromat
* type @Prototype
****************************************************/
(function() {
Date.prototype.toYMD = Date_toYMD;
function Date_toYMD() {
var year, month, day;
year = String(this.getFullYear());
month = String(this.getMonth() + 1);
if (month.length == 1) {
month = “0″ + month;
}
day = String(this.getDate());
if (day.length == 1) {
day = “0″ + day;
}
return year + “-” + month + “-” + day;
}
})();
/***********************************
* How to Use Function with JavaScript
**********************************/
var sqlDate = getDateNow(‘mysql_date'));
/***********************************
* How to Use Function with HTML
**********************************/
<a href=”javascript:void(0);” onClick=”this.innerHTML = getDateNow(‘mysql_date');” title=”click to know Date as SQL Date”>JavaScript Date to MySQL Date</a>
回答by enoyhs
You need to add date with correct format, which is: YYYY-MM-DD HH:MM:SS. 10.3.1. The DATETIME, DATE, and TIMESTAMP Types
您需要以正确的格式添加日期，即：YYYY-MM-DD HH:MM:SS. 10.3.1. DATETIME、DATE 和 TIMESTAMP 类型
So to convert that you need to write something like this:
因此，要转换它，您需要编写如下内容：
var t = new Date();
var YYYY = t.getFullYear();
var MM = ((t.getMonth() + 1 < 10) ? '0' : '') + (t.getMonth() + 1);
var DD = ((t.getDate() < 10) ? '0' : '') + t.getDate();
var HH = ((t.getHours() < 10) ? '0' : '') + t.getHours();
var mm = ((t.getMinutes() < 10) ? '0' : '') + t.getMinutes();
var ss = ((t.getSeconds() < 10) ? '0' : '') + t.getSeconds();
var time_of_call = YYYY+'-'+MM+'-'+DD+' '+HH+':'+mm+':'+ss;
Of course you can shorten all this and stuff like that, but you get the idea.
当然，你可以缩短所有这些和类似的东西，但你明白了。
回答by diagonalbatman
I found a snippet a while back, which may help depending on where you want to convert:
我不久前发现了一个片段，这可能会有所帮助，具体取决于您要转换的位置：
function formatDate(date1) {
return date1.getFullYear() + '-' +
(date1.getMonth() < 9 ? '0' : '') + (date1.getMonth()+1) + '-' +
(date1.getDate() < 10 ? '0' : '') + date1.getDate();
}
