unix_timestamp (MySQL) 的数据类型是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4125947/
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
What is the data type for unix_timestamp (MySQL)?
提问by jzarsuelo
What data type should I use for saving unix_timestampvalue (MySQL)?
我应该使用什么数据类型来保存unix_timestamp值（MySQL）？
回答by Haim Evgi
the type is integer like :
类型是整数，如：
int(11)
is good for indexing and conditions like > < =
有利于索引和条件，如 > < =
回答by OMG Ponies
You want to use the TIMESTAMP data type.
It's stored as an epoch value, but MySQL displays the value as 'YYYY-MM-DD HH:MM:SS'.
您想使用TIMESTAMP 数据类型。
它存储为纪元值，但 MySQL 将该值显示为“YYYY-MM-DD HH:MM:SS”。
回答by Ahmad Zahabi
MySql DateTimedata type store the date in format 'YYYY-MM-DD HH:MM:SS' with range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
MySql TIMESTAMPdata type store the date in format 'YYYY-MM-DD HH:MM:SS' with range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
MySql DateTime数据类型以“YYYY-MM-DD HH:MM:SS”格式存储日期，范围从“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。
MySql TIMESTAMP数据类型以 'YYYY-MM-DD HH:MM:SS' 格式存储日期，范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'世界标准时间。
Unix TIMESTAMPis the number of seconds since 1970-01-01, if you want to store the unix Timestamp in mysqldb you should use int(11)with attribute UNSIGNED(to permit only positive numbers), and if you want to save the number of microseconds you should use bigint(20),..
Unix TIMESTAMP是自 以来的秒数1970-01-01，如果您想将 Unix 时间戳存储在mysqldb 中，您应该使用int(11)with 属性UNSIGNED（仅允许正数），如果您想保存微秒数，您应该使用bigint(20),..
If you want to get the unixtimestampin readable format in your select query
You can use
如果您想unixtimestamp在选择查询中获得可读格式，您可以使用
SELECT FROM_UNIXTIME(CAST(yourtable.start_time as UNSIGNED)) as date_time
SELECT FROM_UNIXTIME(CAST(yourtable.start_time as UNSIGNED)) as date_time
If you are using phpyou can use:
如果您正在使用，php您可以使用：
$unixtimestamp= time();//1544619186
echo(date("Y-m-d", $unixtimestamp));//2018-12-12
If you want to display the datetime using the local timezonein Javascriptuse this function
如果要在Javascript 中使用本地时区显示日期时间，请使用此函数
function timeConverter(UNIX_timestamp){
var date = new Date(UNIX_timestamp*1000);
var year = date.getFullYear();
var month = ("0"+(date.getMonth()+1)).substr(-2);
var day = ("0"+date.getDate()).substr(-2);
var hour = ("0"+date.getHours()).substr(-2);
var minutes = ("0"+date.getMinutes()).substr(-2);
var seconds = ("0"+date.getSeconds()).substr(-2);
return year+"-"+month+"-"+day+" "+hour+":"+minutes+":"+seconds;
}
console.log(timeConverter(value));// 2018-14-12 13:11:33
(In this case the server should return the unixTimestamp as it is:
SELECT yourtable.start_time as date_time)
（在这种情况下，服务器应该返回unixTimestamp，因为它是：
SELECT yourtable.start_time as date_time）
