60

What data type should I use for saving unix_timestamp value (MySQL)?

Shahzad Barkati
  • 2,532
  • 6
  • 25
  • 33
jjz
  • 2,007
  • 3
  • 21
  • 30
  • 4
    http://dev.mysql.com/doc/refman/5.0/en/datetime.html – teemitzitrone Nov 08 '10 at 16:48
  • Is timestamp same as unix_timestamp? – jjz Nov 08 '10 at 16:50
  • @user239431: UNIX_TIMESTAMP is a MySQL function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp – OMG Ponies Nov 08 '10 at 16:59
  • @user239431 no it isnt. unix_timestamp it returns seconds since '1970-01-01 00:00:00' and (mysql) timestamp a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format. if you realy need timestamp (unix) in your application you can save the current datetime with NOW() and return a unix_timestap with UNIX_TIMESTAMP() and still have the full power of MySQL date functions see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html – teemitzitrone Nov 08 '10 at 17:02
  • also worth to read http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime – teemitzitrone Nov 08 '10 at 17:06

4 Answers4

74

the type is integer like :

int(11)

is good for indexing and conditions like > < =

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • 27
    If you use INT you need to remember the signed version only goes from -2147483648 to 2147483647, the upper bound triggers the [year 2038 bug](http://en.wikipedia.org/wiki/Year_2038_problem). You could use INT(11) UNSIGNED or BIGINT - there are [some caveats](http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html) with the latter but it's [massive](http://en.wikipedia.org/wiki/Integer_(computer_science)). Personally I feel safer with MySQL's inbuilt functions but indexing and comparison functions for large datasets may sway you. – William Turrell Mar 24 '13 at 20:21
  • For posterity I +1 William Turrell's comment. UNSIGNED INT would be the way to go from my perspective, but the truest answer will depend on the language you are pulling the data base information into (if coding from scratch). Example: if you are a Java shop UNSIGNED INT maps to a java.lang.Long (see table 5.2 here for MySQL + Java users: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html). This way you save some space in the DB while insuring you do not write code that will hit a bug in 2038. Great point @WilliamTurrell. – Zack Jannsen Feb 03 '16 at 12:25
  • 1
    In terms of the 2038 bug, wouldn't increasing the 11 in `int(11)` to a 12 or 13 be a reasonable measure to avoid the situation? – Brad Hein Nov 29 '17 at 13:35
  • @BradHein The 11 is or display, not the size of what can be stored. The max value of an UNSIGNED int is always 4294967295. See https://dev.mysql.com/doc/refman/5.7/en/integer-types.html. Pump that number into an epoch converter and see the field can hold dates out to roughly Feb 7, 2106. https://www.epochconverter.com/timezones?q=4294967295&tz=America%2FNew_York – Gary Nov 02 '19 at 18:58
  • The time that can be stored doubles with the unsigned flag. 2038-1970=68 years and 2106-1970=136 years. – Gary Nov 02 '19 at 19:05
19

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'.

ksg91
  • 1,279
  • 14
  • 34
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 3
    I believe the question is asking which MySQL data type is best for storing epoch values. TIMESTAMP would not be the answer here, as the only acceptable values for TIMESTAMP are strings of month/day/year. Source: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html – WindedHero Jun 30 '20 at 16:53
5

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)

Community
  • 1
  • 1
Ahmad Zahabi
  • 1,110
  • 12
  • 15
0

BIGINT UNSIGNED NOT NULL

Epoch representation in seconds can fit into INT(11) If the timestamp has milliseconds, INT(20) can be used.

Sisir
  • 2,668
  • 6
  • 47
  • 82
Ramesh Krishnan
  • 21