2

I am trying to create a table with a timestamp column and a virtual (calculated) column that holds a helper key for grouping the rows with timestamps of the same hour. For that I am using the following command in MariaDB / MySql:

CREATE TABLE mytable(t TIMESTAMP, u INT AS (UNIX_TIMESTAMP(t) DIV 3600);

Which returns the following error:

ERROR 1901 (HY000): Function or expression is not allowed for column 'u'

According to the documentation i don't see a reason this should not work. Any ideas?

Claude
  • 21
  • 2

1 Answers1

1

Replace this...

(UNIX_TIMESTAMP(t) DIV 3600)

...with this...

(TIMESTAMPDIFF(HOUR,'1970-01-01 00:00:00',t))

Verified on MariaDB 10.1.14, this should work in any later version.

Since 1970-01-01 00:00:00 is 0 in UNIX_TIMESTAMP(), and DIV 3600 is integer divide, your expression seems to be equivalent to FLOOR() of the number of hours since 1970-01-01 00:00:00... and TIMESTAMPDIFF() appears to provide the same value.

MariaDB seems to be taking UNIX_TIMESTAMP() to be non-deterministic, which is not correct when arguments are provided. The MySQL core may lack the ability to consider built-in functions "sometimes deterministic," or this might be a bug.

But I believe you have a viable workaround using TIMESTAMPDIFF().

Note also, you appear to need the PERSISTENT keyword, because otherwise the virtual column can't be indexed, since the value isn't stored.

ERROR 1901: Key/Index cannot be defined on a non-stored computed column
Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • 1
    I believe this code fails to account for the clients timezone. So, it could get different values in different clients. – Rick James Apr 02 '17 at 15:13
  • And this can't be resolved because the limitation is that it can't depend on data outside the row (like client time zone) - see https://mariadb.com/kb/en/library/generated-columns/#limitations. The only hack I found would be if the client is under your full control and uses stable TZ. Then you can add column with the offset (in hours in your case) that was current at the time of `t`. This column then can be used in computation like `(TIMESTAMPDIFF(HOUR, '1970-01-01', t) - t_tz_offset)` ... but it's super ugly hack as I said before – chipiik Mar 20 '19 at 16:20
  • @chipiik yeah, true. I may have overlooked that since it is extremely rare for me to work with sessions outside of UTC. – Michael - sqlbot Mar 20 '19 at 19:57