[Vtigercrm-developers] Has anyone else had a problem with a user's date format preference interfered with a custom datetime field?
Steve Kenow
skenow at rdspos.com
Thu Dec 19 23:02:52 GMT 2024
Login and set calendar preferences to mm-dd-yyyy
Navigate to a closed ticket
Edit - make no changes
Save
Result: Closed Date (cf_825) is saved as 0000-00-00 00:00:00
DEBUG VT Prepared sql query being executed : UPDATE vtiger_ticketcf SET
cf_825=?,cf_779=?,cf_833=? WHERE ticketid=?
DEBUG VT Prepared sql query parameters : [*05-02-2024 16:07:08*
,0,150,2698841]
*** date formatted as it appears
It is treated as a change and is added to modtracker -
2024-12-19T16:04:47-06:00 DEBUG VT Prepared sql query being executed :
INSERT INTO vtiger_modtracker_detail(id,fieldname,prevalue,postvalue)
VALUES(?,?,?,?)
2024-12-19T16:04:47-06:00 DEBUG VT Prepared sql query parameters :
[4334899,cf_825,*2024-05-02 16:07:08*,0000-00-00 00:00:00]
This is when I 'fixed' the value in the field by manually entering the date
in the expected format -
2024-12-19T16:05:24-06:00 DEBUG VT Prepared sql query being executed :
UPDATE vtiger_ticketcf SET cf_825=?,cf_779=?,cf_833=? WHERE ticketid=?
2024-12-19T16:05:24-06:00 DEBUG VT Prepared sql query parameters : [*2024-05-02
16:07:08*,0,150,2698841]
2024-12-19T16:05:25-06:00 DEBUG VT Prepared sql query being executed :
INSERT INTO vtiger_modtracker_detail(id,fieldname,prevalue,postvalue)
VALUES(?,?,?,?)
2024-12-19T16:05:25-06:00 DEBUG VT Prepared sql query parameters :
[4334900,cf_825,0000-00-00 00:00:00,2024-05-02 16:07:08]
It appears that some fields are always sent in an SQL update, whether or
not they are changed. cf_779 is a checkbox and cf_833 is a decimal field.
Those don't show up as being updated in modtracker.
Also, I do not see any call to DateTimeField->getDBInsertDateValue() for
the values in this field. I am seeing it for other datetime fields on the
record.
I have 1 other custom datetime field in this instance, in Contacts, and the
same thing occurs there. What is it that determines a datetime field exists
and the conversion to the DB is needed?
*[image: 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
[image: Icon 1]
<https://mailtrack.io/trace/link/d5e46ac8cfdec7b8ebdc539696dd45306644b800?w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts-Solutions%2FGrocery&userId=3091118&signature=18b062316c510b1b>
[image:
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>
[image:
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>
[image:
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>
[image:
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>
[image:
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 at rdspos.com (non-emergencies) or
call (952) 934-4002 24/7
On Thu, Dec 19, 2024 at 3:09 AM Alan Lord <alanslists at gmail.com> wrote:
> 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/
>
>
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20241219/75cf0b25/attachment-0001.html>
More information about the vtigercrm-developers
mailing list