Re: DATETIME vs CHAR for "timestamp"
- Date: Fri, 14 Apr 2017 16:07:03 -0400
- From: "shawn l.green" <shawn.l.green@xxxxxxxxxx>
- Subject: 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?
That all depends. Do you...
a) want mysqld to treat that column as an actual temporal value
b) want mysqld to see it as an opaque string of random alphanumeric
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.
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
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:
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.
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/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql