Search code examples
mysqldatabaselamp

Using varchar(14) as datetime in mysql

I'm working with a database that's using varchar(14) as a datetime datatype and in older tables, it's working smoothly as a datetime type with functions like DATEDIFF().

I tried to use this format to create a new table and remain consistent in this project and it's not working with datetime functions. Is there any convert macros, custom data types, or global functions that may be referencing these older columns that I need to update for my new table? Where would I look for these?

Given these two tables: 

 CREATE TABLE `working` (
  `id` varchar(30) NOT NULL DEFAULT '',
  `timecomplete` varchar(14) DEFAULT NULL,
  `count` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`count`),
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

CREATE TABLE `notWorking` (
  `count` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id` varchar(30) NOT NULL,
  `delDate` varchar(14) DEFAULT NULL,
  PRIMARY KEY (`count`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

With the first i can query SELECT id FROM working WHERE DATEDIFF(NOW(), timecomplete) >1; and all is well.

On the second, SELECT id FROM notWorking WHERE DATEDIFF(NOW(), delDate) >1; returns the warning: Incorrect datetime value: '2018040305000' which while correct doesn't help me much.

Solution

  • I had an issue where a varchar(14) wasn't working as a DateTime type in MySQL. It turns out I was missing a single digit. Documentation on this can be found here: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html

    Specifically, this line answered a lot of my questions in terms of this being supported by MySQL.

    MySQL recognizes DATETIME and TIMESTAMP values in these formats:

    As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.