<div dir="ltr"><div></div><div>Login and set calendar preferences to mm-dd-yyyy</div><div>Navigate to a closed ticket</div><div>Edit - make no changes</div><div>Save</div><div><br></div><div>Result: Closed Date (cf_825) is saved as 0000-00-00 00:00:00</div><div><br></div><div>DEBUG VT Prepared sql query being executed : UPDATE vtiger_ticketcf SET cf_825=?,cf_779=?,cf_833=? WHERE ticketid=?</div><div>DEBUG VT Prepared sql query parameters : [<b>05-02-2024 16:07:08</b>,0,150,2698841]</div><div>*** date formatted as it appears<br></div><div><br></div><div>It is treated as a change and is added to modtracker - <br></div><div>2024-12-19T16:04:47-06:00 DEBUG VT Prepared sql query being executed : INSERT INTO vtiger_modtracker_detail(id,fieldname,prevalue,postvalue) VALUES(?,?,?,?)<br>2024-12-19T16:04:47-06:00 DEBUG VT Prepared sql query parameters : [4334899,cf_825,<b>2024-05-02 16:07:08</b>,0000-00-00 00:00:00]</div><div><br></div><div>This is when I 'fixed' the value in the field by manually entering the date in the expected format -</div><div><br></div><div>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=?<br>2024-12-19T16:05:24-06:00 DEBUG VT Prepared sql query parameters : [<b>2024-05-02 16:07:08</b>,0,150,2698841]</div><div><br></div><div>2024-12-19T16:05:25-06:00 DEBUG VT Prepared sql query being executed : INSERT INTO vtiger_modtracker_detail(id,fieldname,prevalue,postvalue) VALUES(?,?,?,?)<br>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]</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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?<br></div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><br></div><table cellpadding="0" cellspacing="0" style="font-family:Arial,sans-serif;font-size:14px;color:rgb(51,51,51)"><tbody><tr><td style="display:flex"><b><img src="https://media.glassdoor.com/sql/640156/retail-data-systems-squarelogo-1412104178471.png" alt="RDS Logo" style="width:60px;height:60px;margin-right:10px"></b><div><b>Steve Kenow</b><br><span style="font-style:italic;font-size:12px">Manager, End-User Services and Support - <a href="https://www.rdspos.com/Minneapolis-Minnesota-Grocery-Point-of-Sale-System" target="_blank">RDS MN</a></span><br><span style="font-size:12px;display:inline-block">Direct: (952) 392-2686<br>Office: (952) 934-4001<br>Email: <a href="mailto:skenow@rdspos.com" style="color:rgb(17,85,204)" target="_blank">skenow@rdspos.com</a></span></div></td></tr><tr><td><table cellpadding="0" cellspacing="0"><tbody><tr><td><br><table cellpadding="0" cellspacing="0"><tbody><tr><td><table cellpadding="0" cellspacing="0" style="margin-top:10px"><tbody><tr><td><a href="https://mailtrack.io/trace/link/d5e46ac8cfdec7b8ebdc539696dd45306644b800?w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts-Solutions%2FGrocery&userId=3091118&signature=18b062316c510b1b" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icon-Shopping-Cart2.jpg" alt="Icon 1" style="width:30px;height:30px;margin-right:10px"></a></td><td><a href="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" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icon-Burger2.jpg" alt="Icon 2" style="width:30px;height:30px;margin-right:10px"></a></td><td><a href="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" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icons-TableService2.jpg" alt="Icon 3" style="width:30px;height:30px;margin-right:10px"></a></td><td><a href="https://mailtrack.io/trace/link/c5c6a63b04b163898650fb7e2fdd7312ca923cdf?w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts-Solutions%2FGeneral-Retail&userId=3091118&signature=1ec7a1d9db94a0f1" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icons-Retail2.png" alt="Icon 4" style="width:30px;height:30px;margin-right:10px"></a></td><td><a href="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" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icon-Fuel2.jpg" alt="Icon 5" style="width:30px;height:30px;margin-right:10px"></a></td><td><a href="https://mailtrack.io/trace/link/16b4ffb1c4363203009e09666cb41ce82f3a39c1?w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%2Fwww.rdspos.com%2FProducts-Solutions%2FRDS-Payment-Services&userId=3091118&signature=84ffb1248e80fb55" style="color:rgb(17,85,204)" target="_blank"><img src="https://www.rdspos.com/portals/0/Icon%20-%20Credit%20Card%20processing.png" alt="Icon 6" style="width:30px;height:30px;margin-right:10px"></a></td></tr></tbody></table><hr style="width:240px;height:1px;margin-top:10px;margin-bottom:6px;background-color:rgb(170,170,170)"><div><br></div></td></tr></tbody></table><font size="2">Support Questions/Issues? Email <a href="mailto:rdshelp@rdspos.com" target="_blank">rdshelp@rdspos.com</a> (non-emergencies) or call (952) 934-4002 24/7</font></td></tr></tbody></table></td></tr></tbody></table></div></div></div><br></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Thu, Dec 19, 2024 at 3:09 AM Alan Lord <<a href="mailto:alanslists@gmail.com">alanslists@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">This is why datetime values should really always be stored in UTC.<br>
<br>
It is possible in MySQL to add a timezone offset to the DATETIME column <br>
but vtiger does not do this as it assumes all datetimes are in UTC (or <br>
whatever is set in config.inc.php). It then uses the User's Preferences <br>
timezone setting to display the correct time to the user.<br>
<br>
FWIW - I just had a long discussion with vtiger about this on the Cloud <br>
version. It's the same. All datetimes are stored in UTC.<br>
<br>
I'd do some tests to check if your theory is correct, but I am not sure <br>
why it would be as the date/time formats are always converted back and <br>
forward between "YYYY-MM-DD HH:MM:SS" and whatever the user's settings <br>
are for.<br>
<br>
HTH<br>
<br>
Alan<br>
<br>
On 18/12/2024 17:03, Steve Kenow wrote:<br>
> Thanks, Alan and Ruben.<br>
> <br>
> Looking at all other datetime fields, the values are all stored as yyyy- <br>
> mm-dd hh:mm:ss and there's no timezone information. The times match the <br>
> timezone of our server (I set the timezone in config.inc.php). All of <br>
> our current users are in the same time zone.<br>
> <br>
> What I'm beginning to think is whatever converts date format for the <br>
> user is being treated as a change in the value of the field so that when <br>
> they save the record, vTiger is processing it without converting it to a <br>
> valid date and saves it as a zero. I've had other instances of fields <br>
> being changed for similar reasons. For example, editing a ticket that <br>
> was assigned to a user that has since been marked inactive would <br>
> reassign the ticket to the first active user in the list.<br>
> <br>
> *RDS Logo*<br>
> *Steve Kenow*<br>
> Manager, End-User Services and Support - RDS MN <<a href="https://www.rdspos.com/" rel="noreferrer" target="_blank">https://www.rdspos.com/</a> <br>
> Minneapolis-Minnesota-Grocery-Point-of-Sale-System><br>
> Direct: (952) 392-2686<br>
> Office: (952) 934-4001<br>
> Email: <a href="mailto:skenow@rdspos.com" target="_blank">skenow@rdspos.com</a> <mailto:<a href="mailto:skenow-" target="_blank">skenow-</a> <br>
> <a href="mailto:O9tEEiBXuwnQT0dZR%2BAlfA@public.gmane.org" target="_blank">O9tEEiBXuwnQT0dZR+AlfA@public.gmane.org</a>><br>
> <br>
> Icon 1 <<a href="https://mailtrack.io/trace/link/" rel="noreferrer" target="_blank">https://mailtrack.io/trace/link/</a> <br>
> d5e46ac8cfdec7b8ebdc539696dd45306644b800? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FGrocery&userId=3091118&signature=18b062316c510b1b> Icon 2 <br>
> <<a href="https://mailtrack.io/trace/link/" rel="noreferrer" target="_blank">https://mailtrack.io/trace/link/</a> <br>
> b0fdfdd837e0d187e174cae0c9479c9b847c4e36? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FQuick-Service%2FPoint-of-Sale- <br>
> Solutions&userId=3091118&signature=b26cf2b23255a2a2> Icon 3 <https:// <br>
> <a href="http://mailtrack.io/trace/link/9c1116af49d5de3a851b38185dbd7ba07d27def2" rel="noreferrer" target="_blank">mailtrack.io/trace/link/9c1116af49d5de3a851b38185dbd7ba07d27def2</a>? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FRestaurant%2FPoint-of-Sale- <br>
> Solutions&userId=3091118&signature=78db9a7d4392c7ed> Icon 4 <https:// <br>
> <a href="http://mailtrack.io/trace/link/c5c6a63b04b163898650fb7e2fdd7312ca923cdf" rel="noreferrer" target="_blank">mailtrack.io/trace/link/c5c6a63b04b163898650fb7e2fdd7312ca923cdf</a>? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FGeneral-Retail&userId=3091118&signature=1ec7a1d9db94a0f1> <br>
> Icon 5 <<a href="https://mailtrack.io/trace/" rel="noreferrer" target="_blank">https://mailtrack.io/trace/</a> <br>
> link/9d0a121b09b0d5e1ba1c5be04c5c2b8d969b7d2c? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FConvenience-Store%2FNCR-Radiant-Convenience-Store-POS- <br>
> System&userId=3091118&signature=112f29b42fe5a787> Icon 6 <https:// <br>
> <a href="http://mailtrack.io/trace/link/16b4ffb1c4363203009e09666cb41ce82f3a39c1" rel="noreferrer" target="_blank">mailtrack.io/trace/link/16b4ffb1c4363203009e09666cb41ce82f3a39c1</a>? <br>
> w=anNrb2xha0ByZHNwb3MuY29t&url=https%3A%2F%<a href="http://2Fwww.rdspos.com" rel="noreferrer" target="_blank">2Fwww.rdspos.com</a>%2FProducts- <br>
> Solutions%2FRDS-Payment-Services&userId=3091118&signature=84ffb1248e80fb55><br>
> <br>
> ------------------------------------------------------------------------<br>
> <br>
> Support Questions/Issues? Email rdshelp- <br>
> <a href="mailto:O9tEEiBXuwnQT0dZR%2BAlfA@public.gmane.org" target="_blank">O9tEEiBXuwnQT0dZR+AlfA@public.gmane.org</a> <mailto:<a href="mailto:rdshelp-" target="_blank">rdshelp-</a> <br>
> <a href="mailto:O9tEEiBXuwnQT0dZR%2BAlfA@public.gmane.org" target="_blank">O9tEEiBXuwnQT0dZR+AlfA@public.gmane.org</a>> (non-emergencies) or call (952) <br>
> 934-4002 24/7<br>
> <br>
> <br>
> <br>
> On Tue, Dec 17, 2024 at 7:16 PM Rubén A. Estrada Orozco <rulotec1- <br>
> <a href="mailto:Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org" target="_blank">Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org</a> <mailto:<a href="mailto:rulotec1-" target="_blank">rulotec1-</a> <br>
> <a href="mailto:Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org" target="_blank">Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org</a>>> wrote:<br>
> <br>
> If all users are located in the same time zone, I would recommend<br>
> not using UTC for the database but the timezone of the users. It's<br>
> more convenient (what you see in the db is the same thing you see in<br>
> the UI)<br>
> <br>
> On Tue, Dec 17, 2024 at 11:48 AM Alan Lord <alanslists-<br>
> <a href="mailto:Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org" target="_blank">Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org</a><br>
> <mailto:<a href="mailto:alanslists@gmail.com" target="_blank">alanslists@gmail.com</a>>> wrote:<br>
> <br>
> One other thing. Datetime columns in the database should ALWAYS be<br>
> stored in UTC. They are converted as the user views them based<br>
> on not<br>
> only the format, but also their timezone.<br>
> <br>
> Al<br>
> <br>
> On 17/12/2024 17:40, Alan Lord wrote:<br>
> > I think I'd probably use a custom Event Handler rather than a<br>
> workflow<br>
> > and make the field read-only and update it directly using a<br>
> query when<br>
> > the triggering event occurs. You could add a simple test to<br>
> say if the<br>
> > field is not null or not empty, then don't update it - ever.<br>
> ><br>
> > HTH<br>
> ><br>
> > Al<br>
> ><br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a> <<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a>><br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a> <<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a>><br>
> <br>
> <br>
> _______________________________________________<br>
> <a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
<br>
<br>
_______________________________________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a></blockquote></div>