Re: DATETIME vs CHAR for "timestamp"

2017-04-24 Thread SSC_perl
> On Apr 14, 2017, at 1:07 PM, shawn  wrote:
> That all depends. Do you...

Hi Shawn,

I thought I had replied to your response, but it looks like I didn’t.  
Thank you for your email.  It was a thorough response and the links were very 
helpful, as well.  I’ve settled on both DATE and DATETIME, depending on whether 
the time is needed or not, which means I’ll have to change some of my code, but 
that will only strengthen the script in the long run.

Thanks again,
MySQL General Mailing List
For list archives:
To unsubscribe:

Re: DATETIME vs CHAR for "timestamp"

2017-04-14 Thread shawn

On 4/14/2017 3:11 PM, SSC_perl wrote:

I have creation date/time fields in my script that are formatted as 
|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.

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

Become certified in MySQL! Visit 
for details.

MySQL General Mailing List
For list archives:
To unsubscribe: