[Vtigercrm-developers] Has anyone else had a problem with a user's date format preference interfered with a custom datetime field?

[Alan Lord]

This is why datetime values should really always be stored in UTC.

It is possible in MySQL to add a timezone offset to the DATETIME column 
but vtiger does not do this as it assumes all datetimes are in UTC (or 
whatever is set in config.inc.php). It then uses the User's Preferences 
timezone setting to display the correct time to the user.

FWIW - I just had a long discussion with vtiger about this on the Cloud 
version. It's the same. All datetimes are stored in UTC.

I'd do some tests to check if your theory is correct, but I am not sure 
why it would be as the date/time formats are always converted back and 
forward between "YYYY-MM-DD HH:MM:SS" and whatever the user's settings 
are for.

HTH

Alan

On 18/12/2024 17:03, Steve Kenow wrote:
> Thanks, Alan and Ruben.
> 
> Looking at all other datetime fields, the values are all stored as yyyy- 
> mm-dd hh:mm:ss and there's no timezone information. The times match the 
> timezone of our server (I set the timezone in config.inc.php). All of 
> our current users are in the same time zone.
> 
> What I'm beginning to think is whatever converts date format for the 
> user is being treated as a change in the value of the field so that when 
> they save the record, vTiger is processing it without converting it to a 
> valid date and saves it as a zero. I've had other instances of fields 
> being changed for similar reasons. For example, editing a ticket that 
> was assigned to a user that has since been marked inactive would 
> reassign the ticket to the first active user in the list.
> 
> *RDS Logo*
> *Steve Kenow*
> Manager, End-User Services and Support - RDS MN <https://www.rdspos.com/ 
> Minneapolis-Minnesota-Grocery-Point-of-Sale-System>
> Direct: (952) 392-2686
> Office: (952) 934-4001
> Email: skenow at rdspos.com <mailto:skenow- 
> O9tEEiBXuwnQT0dZR+AlfA at public.gmane.org>
> 
> Icon 1 <https://mailtrack.io/trace/link/ 
> d5e46ac8cfdec7b8ebdc539696dd45306644b800? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FGrocery&userId=3091118&signature=18b062316c510b1b>	Icon 2 
> <https://mailtrack.io/trace/link/ 
> b0fdfdd837e0d187e174cae0c9479c9b847c4e36? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FQuick-Service%2FPoint-of-Sale- 
> Solutions&userId=3091118&signature=b26cf2b23255a2a2>	Icon 3 <https:// 
> mailtrack.io/trace/link/9c1116af49d5de3a851b38185dbd7ba07d27def2? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FRestaurant%2FPoint-of-Sale- 
> Solutions&userId=3091118&signature=78db9a7d4392c7ed>	Icon 4 <https:// 
> mailtrack.io/trace/link/c5c6a63b04b163898650fb7e2fdd7312ca923cdf? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FGeneral-Retail&userId=3091118&signature=1ec7a1d9db94a0f1>	 
> Icon 5 <https://mailtrack.io/trace/ 
> link/9d0a121b09b0d5e1ba1c5be04c5c2b8d969b7d2c? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FConvenience-Store%2FNCR-Radiant-Convenience-Store-POS- 
> System&userId=3091118&signature=112f29b42fe5a787>	Icon 6 <https:// 
> mailtrack.io/trace/link/16b4ffb1c4363203009e09666cb41ce82f3a39c1? 
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts- 
> Solutions%2FRDS-Payment-Services&userId=3091118&signature=84ffb1248e80fb55>
> 
> ------------------------------------------------------------------------
> 
> Support Questions/Issues? Email rdshelp- 
> O9tEEiBXuwnQT0dZR+AlfA at public.gmane.org <mailto:rdshelp- 
> O9tEEiBXuwnQT0dZR+AlfA at public.gmane.org> (non-emergencies) or call (952) 
> 934-4002 24/7
> 
> 
> 
> On Tue, Dec 17, 2024 at 7:16 PM Rubén A. Estrada Orozco <rulotec1- 
> Re5JQEeQqe8AvxtiuMwx3w at public.gmane.org <mailto:rulotec1- 
> Re5JQEeQqe8AvxtiuMwx3w at public.gmane.org>> wrote:
> 
>     If all users are located in the same time zone, I would recommend
>     not using UTC for the database but the timezone of the users. It's
>     more convenient (what you see in the db is the same thing you see in
>     the UI)
> 
>     On Tue, Dec 17, 2024 at 11:48 AM Alan Lord <alanslists-
>     Re5JQEeQqe8AvxtiuMwx3w at public.gmane.org
>     <mailto:alanslists at gmail.com>> wrote:
> 
>         One other thing. Datetime columns in the database should ALWAYS be
>         stored in UTC. They are converted as the user views them based
>         on not
>         only the format, but also their timezone.
> 
>         Al
> 
>         On 17/12/2024 17:40, Alan Lord wrote:
>          > I think I'd probably use a custom Event Handler rather than a
>         workflow
>          > and make the field read-only and update it directly using a
>         query when
>          > the triggering event occurs. You could add a simple test to
>         say if the
>          > field is not null or not empty, then don't update it - ever.
>          >
>          > HTH
>          >
>          > Al
>          >
> 
>         _______________________________________________
>         http://www.vtiger.com/ <http://www.vtiger.com/>
> 
>     _______________________________________________
>     http://www.vtiger.com/ <http://www.vtiger.com/>
> 
> 
> _______________________________________________
> http://www.vtiger.com/