Datetime - how to display UTC datetime from DB, in a different timezone in Tables


#1

I’m having problems with the DateTime fields in my models.

In my models, i want the datetime fields, stored in mysql in UTC.
The system that is running the atk and my project is in UTC+3.
I want the Grids and cruds and tables showing this field in UTC+3

Right now, i’m checking the values in DB, and the field in DB shows 19:37:00

The grid is also showing this field as 19:37:00.

When i want to update this field through my atk project, i get the datetime from date(). Which gives the time in current timezone. When i set this in model, and save it, it converts it into UTC from current time zone. So DB values are in UTC. Good.

But when it loads it up, the Persistance_SQL _typecastLoadField function sets the datetime class timezone as date_default_timezone_get() which is the current timezone.

Then at somepoint, UI’s _typecastSaveField is called and this sets the timezone back to UTC (as it is default for ATK) and the grid shows the values in UTC.

My question is:
What is the correct way of making table components show the value in system’s current timezone, while storing those in UTC in DB.


#2

Hi,

had a related issue some time ago, see if that info helps you:

I am not using this code any more and do not have any of these issues any more, and also cant tell if my solution then was a good one.


#3

I checked that subject, but it solves an issue, luckily i’m not having here, which is storing model datetime values in different timezone other than the atkdefault UTC. the persist_timezone property. Good and useful.

I happen to prefer storing values in DB in UTC timezone. But if that was not the case, knowing the persist_timezone property is the solution i guess.

I was wondering if there was a display_timezone, or render_timezone kinda property somewhere. Probably in the Field class. Doesn’t seem to be the case, or i missed it like i missed a lot of things earlier.

While waiting for a solution suggestion from experienced ATK users, i went with this one:

Extend \atk4\ui\persistence, and override _typecastSaveField (hope i got the name right) so that it does not convert the timezone of the datetime value back to default UTC just before table renders it. My override just copies the _typecastSaveValue from the parent, with the only change that it does

$v-setTimeZone(new $tz_class(date_default_timezone_get()));

instead of

$v-setTimezone(new $tz_class($f-persist_timezone));

So now, the all Datetime fields ever used in my App class are being rendered as servers timezone, but the values are stored in UTC in the DB.

One consistency issue with this was, i’m using date() to get the dates to store in datetime fields. date() puts out in local timezone. I set the model filed with this, and values are converted and stored in UTC. If i use date() again, to produce some date to compare, say, use in $model->addCondition( ‘recTime’, ‘>’, $refDate ), it is wrong. Because, the addCondition doesn’t do a timezone operation here, you end up comparing localtimezone date with a UTC value in DB. So i’m using gmdate() instead of date() when i’m using it for addCondition, or such. Which feels kind of inconsistent, sometimes using date, and sometimes gmdate.


#4

Hi,

glad you could fix it. Two toughts:

  1. Maybe setting the Persistence_UI->datetime_format to something with parameter O in it (adds something like +0300) does the job? Can be done in YourCustomAppClass->__constuct(), so Persistence_UI.php itself can stay untouched

  2. Seems like a bug, post it in Github issues :slight_smile:


#5

It is working fine with the custom ui_persistence for sure, but i’m still looking for ways to do this better, with an untouched ui_persistence, like you mentioned. I thought about it, i think this is not a bug, it’s just something implemented in this way. So the datetime values are converted to Atk default (UTC) and then displayed.

The datetime format ‘O’ or something, would just show the time zone in the formatted date. It wont change it.

In my opinion, with limited experience in the whole ATK stuff, having a ‘$default_timezone’ or ‘$ui_timezone’ kind of a member in this ui_persistence class would be good. With a default value of null, which then uses the field’s default time zone, if not, the given timezone for date related fields. I guess then i could just initialize this value to ‘Europe/Berlin’ and have all dates converted to this timezone just for the ui. Or set it to browser’s timezone so it’s dynamic per http request. Not touching the ui_persistence class at all.

Maybe i should post a feature request? or just implement this and send a pull request. I don’t know how it works really, never been a part of a github project in that way… :slight_smile:


#6

ATK tries to always store datetime in database using UTC. If you notice any problems with that - could you report them on github? Thanks!


#7

I think that is a good thing ATK maintains time data in UTC. Seems to be fine no issues there.

I’m not sure if it is a good thing, trying to display(ui) it also in UTC.

It seems to me storage is computer related, and a standard reliable form is better. Display is user related, so convenience is better, which is user’s own timezone.

So how would you display the UTC datetime, in say ‘UTC+2’ in a Table?


#8

Date is always displayed in the configured timezone. date_default_timezone_set() can be used to set the desired timezone.

You can let user configure that timezone or read it from JavaScript setting just update default timezone and you should be ok.