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)