It appears that column data type TIME (not datetime, just the time of day, for example 12:00) is not supported in Magento 2.

I need to store a time of day, how to work around this?
How should I go about it with the tools and datatypes that are available?

I thought about saving a normal but distinctive date (like 1970-01-01 12:00:00 which is distinctive enough not to be confusing to anyone browsing the data) and just extracting the time part from it in the UI, but it's horrid and makes me cringe inside.

Is there a more elegant way?

  • Could you create a TIME format if it doesn't exist already?
    – scrowler
    Apr 17, 2016 at 18:04
  • @RobbieAverill attempted to a) override & extend the class Magento\Framework\DB\Ddl\Table with my own additions and b) edit Magento\Framework\DB\Ddl\Table directly, but that didn't help (got all sort of errors on setup:upgrade ). Could you be more specific with what you mean by creating a TIME format? Zend db should support time data type, but 1. editing and adding 'TIME' column type in class Magento\Framework\DB\Ddl\Table 2. calling Table->addColumn with 'TIME' type and any combination of options just causes [Zend_Db_Exception] Invalid column definition data Apr 18, 2016 at 20:17

3 Answers 3


I solved this by using DATETIME initially

$table = $installer->getConnection()
            \Magento\Framework\DB\Ddl\Table::TYPE_DATETIME, null,
           ['unsigned' => true, 'nullable' => true],
            'Comment about table'


and then just using raw sql execution with


And that was it, problem solved.

  • It's working. Thanks
    – ZealousWeb
    Oct 13, 2021 at 7:03

Magento ORM does not implement exotic date types. You have two options:

  1. Rewrite every single DDL class in Magento 2.0 Framework and introduce a headache to maintain it with every new release of Magento 2.0 as it might break.

  2. Use another available datatype, not necessarily a date related one, because it might introduce performance degradation. You can try to use INT that is a digit only representation of time. Then, for example, 10:11:43 will be represented as such value in integer column 101143. And you still can do the same operations on MySQL level as with regular TIME time (BETWEEN, <, >, etc). There is no performance degradation in using int instead of time. (I don't recommend VARCHAR representation as it is slower to search over its index)


If I had to be honest: do it 'The Magento Way™'. That's the way you're least likely to get headaches with forward compatibility. I know, I'd like to see a TIME-type as well, but if Magento only supports DATETIME (most likely due to abstraction of the datasource), use that instead, even if it makes you cringe.

Who knows... maybe one day Magento might decide that version 2.x is going to support a newer, faster database that doesn't support TIME. Having rewritten your DDL's (or altering your raw tables on the fly) might have some nasty side-effects then.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.