OpenSuse 15.1

This is, unfortunately, intentional.

The behavior was changed as a fix for a bug MDEV-21249. The logic is that if the function's return value depends on the session variables, it cannot be used in a persistent generated column or in an indexed virtual column. When we did it, we missed the fact that FROM_UNIXTIME depends on the current session @@time_zone value.

Thus, if you change @@time_zone and select from your existing table, you will see that column's value won't match the result of cast(from_unixtime(`ts`) as date)

Thank you Sergei.

Do you know of any work around ? Basically we use this virtual column to convert from unix_timestamp to a datetime for easier user interaction...

Due to Virtual column not supporting Stored Procedure / Stored Functions the real solution should be able to have a function FROM_UNIXTIME(ts, timezone), if you can send me some link on how to create such a function I will try to provide a pull request. (it should be a matter of read a value passed to the function instead of the global one right ?)

As a side note documentation need to be updated in https://mariadb.com/kb/en/generated-columns/ you can read
In MariaDB 10.2.1 and later, the following statements apply to expressions for generated columns:
Non-deterministic built-in functions are supported in expressions for PERSISTENT generated columns.

An easy workaround would be to make the column VIRTUAL and not PERSISTENT. You'll save some disk space too.

Correct, FROM_UNIXTIME(ts, timezone) this is how we typically solve this. E.g. MDEV-11553 introduced a 3-argument version of DATE_FORMAT exactly for this reason. If you're still interested in doing a pull request after the workaround above, just say so and I'd be happy to provide some pointers.

Thanks! The documentation got it wrong for some reason. I've corrected it now.

By the way, Jira is sending you email notifications for new comments and status changes, and they all bounce back. Is this your email address still valid?

Hi Sergei, you are right, mail was of a previous company...

This is a nice suggestion about the VIRTUAL, it will not be index-able, but yes is a nice idea...

About the MDEV-11553, Yes is fine for me to try out to add FROM_UNIXTIME(ts, timezone)

I will start from https://github.com/MariaDB/server/commit/80b9ce359312c1520623531bf173ee048317fee3

Thank you

Hi @Sergei Golubchik, I sadly had the time to prepare the patch
I hope formatting and all is correct, there is a decent amount of comment in the code too..
If you think is ok I will do a merge request

https://gist.github.com/RoyBellingan/3c9d95c3fbdbeb61aaeb9e69d84f963a

Hello, I have the same problem and I think it should not be marked as closed yet, until Roy's patch (thank you!) is integrated. I hope it will already be merged into 10.4.13.

RoyBellingan, how's your patch going?

danblack, there's that problem that 2-argument version of FROM_UNIXTIME already exists. Like (from the manual)

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');

I don't know who thought it'd be a great idea to embed DATE_FORMAT into FROM_UNIXTIME to save few keystrokes, but here we are. We can, of course, say that when the format is NULL it means the default format of '%Y-%m-%d %H:%i:%S.%f' and indeed, that's what RoyBellingan did in his patch. But it looks like a hack (which is it).

It's a bit simpler for UNIX_TIMESTAMP that only has a 1-argument form, so 2-argument.

One option could be something like FROM_UNIXTIME(expr1 [, expr2] [USING time_zone ]) and the same for UNIX_TIMESTAMP(). But special keyword syntax is kind of evil too

Thank you for the feedback.
Daniel Black suggested me to "don't go overboard in accepting multiple versions of empty format strings, NULL is probably sufficient, 0 looks odd, and "" is a bit inconsistent with the two args form (will generate a different result, if you happen to specify a timezone), so keep it simple"

I have now to understand how to write and run the test.

Please, can someone change the status of this issue to Open?

I still have this problem, and the workaround with PERSISTENT does not work for me because my database is large, there is JSON involved and the select times would increase dramatically.

I really would need that patch Roy Bellingan, if it is finished yet, and I hope it is included in the next version of MariaDB.

Hi Daniel, I did a few test case https://gist.github.com/RoyBellingan/3c9d95c3fbdbeb61aaeb9e69d84f963a

3 new files, in the main section, called timezone5, I used timezone4 as a template
I also had to change the main/parser.test and remove the check in using a 3 value format.

But IS NOT WORKING when used under a view, because in the FRM file is not saved the 3 arg but only the first.... still investiganting why.

RoyBellingan sorry for getting distracted on this. I'm around so start up a zulip topic, ping me and show a ref to your current code and I'll give it a closer look.

Good to hear this is still worked on. Any progress? Actually, to me it looks more like a mere syntax problem.

@patrick it became much a bit more difficult than expect...
Ongoing developement is here https://github.com/RoyBellingan/server/pull/1 but at the moment Is stalled due to me beeing a bit busy.

What needs to be done now is a good numer of test cases, covering many area...

I think this is workaround-able. We can simply avoid using from_unixtime() and instead rely upon the fact that date_add ignores timezones (this is a feature, not a bug, right?); so this is an equivalent to from_unixtime always in UTC:-

drop temporary table if exists test1;

create temporary table test1 (

ts bigint not null,

asdate datetime as (date_add("1970-1-1 00:00:00", interval ts second)) persistent

) engine=innodb;

Or if you want a different timezone then just convert explicitly:-

drop temporary table if exists test1;

create temporary table test1 (

ts bigint not null,

asdate datetime as (convert_tz(date_add("1970-1-1 00:00:00", interval ts second), "UTC", "Europe/London")) persistent

) engine=innodb;

insert into test1 (ts) values (1), (1606993464), (1603587540), (1603587600);

select * from test1;

This is a very cool idea!
Amazing