Why does custom DateTime calculated from database field display incorrect time?

Normally the date in the DB is stored in UTC and is converted to local timezone when displayed. It seems that having the field like that breaks this.

So like, bug broken? Would there be a way to work around this? All our users are in the same time zone, so I would be fine with a hacky solution temporarily.

Question 1)

Your implementation uses an unbound field to calculate the Appointment Date each time Case records are returned by Calculation utilizing PXDBScalar to look up CRActivity Records of type Appointment.

Does PXDBScalar return the UTC date without PXFormula being implemented?

Questions 2/3)

Are you using PXFormula to aggregate the StartDate from your Activities in case of multiple records?

OR

Are you using PXFormula to set the AppointmentDate = Now if no records are found from PXDBScalar?

[PXFormula(typeof(PX.Objects.CS.TimeZoneNow))] and [PXFormula(typeof(TimeZoneNow))]

Is ref to a sealed class that fetches the Time in the Profiled/Site Pref set time zone

And is implemented on CRActivity.StartDate to capture the current time

CRActivity.StartDate implements the EPStartDateAttribute to lookup the timezone and format the field after the database returns the UTC value.

My recommendation would be to remove PXFormula from your unbound field and decorate it with EPStartDate and/or create an attribute of your own to display the Date in the format you need for your implementation

@tvars71 

I was going to remove [PXFormula] from the code block, but I must not have saved it. That was not supposed to be in this post. That was something I tried as it was unclear to me what it did. I have tried it without that, and it makes no difference. [PXDBScalar] executes fine without it and does return UTC time.

I attempted to use [EPStartDate] but receive the error ‘Invalid cache type. CRActivity is expected.’

I’m afraid defining an attribute from scratch is slightly outside of my current skillset.

@Deetz  try this [PXDateAndTime(UseTimeZone = true)] in place of

 [PXDateAndTime(InputMask = "g", DisplayMask = "g")]

and see if corrects the display

@tvars71 - I already tried that. Unfortunately, it does not help.

Did several experiments  with different tricks and this is definitely returning UTC time. 

Seems that even though PXDateAndTimeAttribute inherits from PXDateAttribute its not running the PXDBScalar result thru the TimeZone conversion. Acumatica will have to take a look at this.

As a workaround, You can catch this in an event and try to reformat the UTC time into the Local format or add a standard DateTimeOffset since everyone is in the same time zone.

@tvars71 

I got it to return the correct result after modifying it in a field selecting event, but this does not correct it for a GI. Is there a way I can achieve both?

     #region UsrAppointmentDate
        [PXDateAndTime(DisplayMask = "g")]
        [PXDBDatetimeScalar(typeof(Search<CRActivity.startDate,
            Where<CRActivity.refNoteID, Equal<CRCase.noteID>>>), PreserveTime = true, UseTimeZone = true)]
        [PXUIField(DisplayName = "Appointment Date", Enabled = false)]
        public virtual DateTime? UsrAppointmentDate { get; set; }
        public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
        #endregion

You sir, are fantastic. Thank you!

     #region UsrAppointmentDate
        [PXDateAndTime(DisplayMask = "g")]
        [PXDBDatetimeScalar(typeof(Search<CRActivity.startDate,
            Where<CRActivity.refNoteID, Equal<CRCase.noteID>>>), PreserveTime = true, UseTimeZone = true)]
        [PXUIField(DisplayName = "Appointment Date", Enabled = false)]
        public virtual DateTime? UsrAppointmentDate { get; set; }
        public abstract class usrAppointmentDate : PX.Data.BQL.BqlDateTime.Field<usrAppointmentDate> { }
        #endregion

Is there any way to do this in a GI field (convert to local user timezone from UTC) without a customization?  If so, can you show an example of the formula that would do this?  I know it is supposed to be automatic using the standard datetime field selection in a GI (such as to show the created and modified times for a record along with the date), however it doesn’t show the time (only the date) unless you enter it as a field formula (such as in the Sales Orders GI here).

I doubt unless there is already a field in the system which can be used as Schema Field for the column in GI.

So the million dollar question: Is there another field to use as a schema field?  

Seems really odd that this isn’t built in functionality for datetime fields to show the time in the local time.  Otherwise why ask each user what timezone they’re in for their profile?

Good question.  The reason, let me explain - audit fields viz. CreatedDateTime and LastModifiedDateTime are stored in UTC using system attributes PXDBCreatedDateTime and PXDBLastModifiedDateTime respectively.  These attributes default display format is d - short date, that is why we see only the date portion.  If we modify this to say g or G - general date/time format, we then can see date and time. See example below for CreatedDateTime field that sets InputMask property to G.  Standard formats can be found here: Standard date and time format strings | Microsoft Docs)  

    public class SOOrderExt : PXCacheExtension<PX.Objects.SO.SOOrder>
    {
        #region CreatedDateTime	
        [PXDBCreatedDateTime(InputMask = "G")]
        [PXUIField(DisplayName = PXDBLastModifiedByIDAttribute.DisplayFieldNames.CreatedDateTime, Enabled = false, IsReadOnly = true)]
        public DateTime? CreatedDateTime { get; set; }
        #endregion
    }

With this information, now we can search in Source Code screen for InputMask = “g” or “G” to find the field and use that field as Schema Field in GI.  I did a quick search and found many fields, see below.  

nmansinha wrote:

 

fosterjeff01 wrote:

So the million dollar question: Is there another field to use as a schema field?  

 

Seems really odd that this isn’t built in functionality for datetime fields to show the time in the local time.  Otherwise why ask each user what timezone they’re in for their profile?

Good question.  The reason, let me explain - audit fields viz. CreatedDateTime and LastModifiedDateTime are stored in UTC using system attributes PXDBCreatedDateTime and PXDBLastModifiedDateTime respectively.  These attributes default display format is d - short date, that is why we see only the date portion.  If we modify this to say g or G - general date/time format, we then can see date and time. See example below for CreatedDateTime field that sets InputMask property to G.  Standard formats can be found here: Standard date and time format strings | Microsoft Docs)  

    public class SOOrderExt : PXCacheExtension<PX.Objects.SO.SOOrder>
    {
        #region CreatedDateTime	
        [PXDBCreatedDateTime(InputMask = "G")]
        [PXUIField(DisplayName = PXDBLastModifiedByIDAttribute.DisplayFieldNames.CreatedDateTime, Enabled = false, IsReadOnly = true)]
        public DateTime? CreatedDateTime { get; set; }
        #endregion
    }

With this information, now we can search in Source Code screen for InputMask = “g” or “G” to find the field and use that field as Schema Field in GI.  I did a quick search and found many fields, see below.  

 

Appreciate the response, but I must not be understanding.  When I use that field as the schema field it doesn’t seem to change it from UTC to local.  Do I not just put [PXDBCreatedDateTime(InputMask = "g")] in the schema field, as in the GI example below?

Thanks for the guidance. New to Acumatica this year and still getting some of the nuances.

As the name suggest, Schema Field only takes field name whose characteristics (format) needs to be applied.  So no - you can’t do that.

In my previous reply, I mentioned about searching field with the required format in Source Code screen.  The resulting search contains fields you can use in the Schema Field column.  You do that by simply adding the DAC in the Tables, no need to define any relations.  See screenshots below:

THANK YOU SO MUCH!!!!  IT WORKS!  Our long national nightmare is finally over!   Really appreciate the patient and detailed explanation on this.  I’ve fixed several GIs already that were driving our users crazy!

Jeff