Menu
I have simple question. Which format of time is better for storing in mysql, epoch or standard date-time (human readable format). I want to know what format will be better later in table which stores millions of rows, regarding speed of select statements.
454
MySQL - UNIX_TIMESTAMP() Function Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.
15, “MySQL Server Time Zone Support”.) unix_timestamp is an internal timestamp value representing seconds since '1970-01-01 00:00:00' UTC, such as produced by the UNIX_TIMESTAMP() function. If format is omitted, this function returns a DATETIME value. If unix_timestamp or format is NULL , this function returns NULL .
UNIX_TIMESTAMP() function When this function used with a date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC. The argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD.
In a MySQL database, the DATE_FORMAT() function allows you to display date and time data in a changed format. This function takes two arguments. The first is the date/datetime to be reformatted; this can be a date/time/datetime/timestamp column or an expression returning a value in one of these data types.
You want the unix epoch. I've done many extremely massive MySQL databases, one of which is a data tracking application that logs millions of records every day for some users.
The reasons epoch is better than datetime:
int is only 4 bytes versus 8 for datetime - better for storage/memory and ultimately indexing/performance. When you're dealing with millions of rows, it really mattersdatetime you need convert strings that are not stored with timezone ident but with the epoch you only need to know the timezone converting to
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
