CODEWITHSUNDEEP
mysqlsql
user9377278
user9377278

Reputation:

How to store date in unix millisecond in Mysql?

How to store current date in MYSQL in UNIX milliseconds like this date: 1388880000000?

Now I store this in datetime.

Upvotes: 0

Views: 2743

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Don't. DateTime values should be stored as DateTime, unless you have a really good reason for storing them otherwise (like for supporting date and time values outside of the min/max values of DateTime), I would suggest to should leave it in a DateTime column.

You can always manipulate how you return them from the database during Select or in the presentation layer. To return the unix time from DateTime, MySql provides a built in method called UNIX_TIMESTAMP. To return the number of milliseconds, simply multiply by 1000, since the unix timestamp is the number of seconds since January 1st, 1970 (not including leap seconds). If you want to store unix time, you will have to use an int data type.

Please note that if you do store unix time instead of storing the actual DateTime value in a DateTime data type column, you will loose the ability to use all the datetime built in database functions easily. For instance, if you want to find out how many rows belong to a specific month, you will first have to translate the data from int to datetime, and only then you will be able to calculate that.

You will also lose accuracy (since unix time is inaccurate even on the 1 second resolution, because it ignores leap seconds).

So, to conclude - When the database offers you a data type that fits the data, don't go storing that data using a different data type. use the Date data type if you only want to store dates, the DateTime data type if you want to store datetime values, and the Time data type if you want to store a specific time in the day.

P.S.
When dealing with date time values, Especially if you have to deal with clients from multiple locations, ALWAYS store only UTC date times in your database, unless, of course, you want to go mad.

Upvotes: 2

Related Questions