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
