What is the data type for unix_timestamp (MySQL)?
Solution 1:
the type is integer like :
int(11)
is good for indexing and conditions like > < =
Solution 2:
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'.
Solution 3:
MySql DateTime data 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 TIMESTAMP data 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.
Unix TIMESTAMP is the number of seconds since 1970-01-01
, if you want to store the unix Timestamp in mysql
db 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)
,..
If you want to get the unixtimestamp
in readable format in your select query
You can use
SELECT FROM_UNIXTIME(CAST(yourtable.start_time as UNSIGNED)) as date_time
If you are using php
you can use:
$unixtimestamp= time();//1544619186
echo(date("Y-m-d", $unixtimestamp));//2018-12-12
If you want to display the datetime using the local timezone in Javascript use this function
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
)