Web lists-archives.com

Re: DATETIME vs CHAR for "timestamp"






On 4/14/2017 3:11 PM, SSC_perl wrote:
	I have creation date/time fields in my script that are formatted as YYYY|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type in MySQL to DATETIME, or would it be better in terms of speed and efficiency to set it as char(19)?  Or would it not make a difference?

Thanks,
Frank



That all depends. Do you...

a) want mysqld to treat that column as an actual temporal value

or

b) want mysqld to see it as an opaque string of random alphanumeric characters

As you appear to have referred to this as a "creation date/time" tracking field it appears you want this to be treated like a temporal value so that you can easily do things like

SELECT ... WHERE create_date > NOW() - interval 7 days ;

If it's a temporal column, you can use functions like those in the next URL against it. If it's a string-type column, you can't unless you first convert your string into a temporal data type.

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html


My suggestion is to use a native temporal data type (I recommend DATETIME) and that you review this section on how to format temporal literals (so that you can pass them easily from your application into MySQL)
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

Using the correct data type is important to performance. You want to avoid forcing the server to perform too many implicit type conversions. Those usually nullify any performance improvements an index on those columns might provide:
https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

And the native DATETIME data type only needs 8 bytes to store its data while your CHAR(16) may need up to 64 bytes of storage.
https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql