Magento 2 datatypes. Need TIME (of day)

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

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.