7

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? – Robbie Averill Apr 17 '16 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 – Kampaviineri Apr 18 '16 at 20:17
2

I solved this by using DATETIME initially

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

`

and then just using raw sql execution with

$installer->run('ALTER TABLE MYTABLE MODIFY MYCOLUMN TIME');

And that was it, problem solved.

1

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)

0

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, privacy policy and cookie policy

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