Solved

Data field best practices

  • 17 January 2023
  • 1 reply
  • 485 views

Userlevel 7
Badge +5

I have some questions around PXDBDate fields

I’m trying to understand when does a date field care about UTC and when does it not?  Looking at SODate.OrderDate I see that it doesn’t store any time value. Jan 1st, 2023 is always that date regardless of the time when the order was placed or what my timezone setting is.

I added a date field to SO Line and when I update the value of the field there is no time component. And, yet, the column is displayed in UTC. I’m in PST (-8) so if I enter 2023-02-01 with no time component and then change my timezone, the display value changes. The database is holding no time component.

So my two questions are:

  1. How do I set up a Date field that does act like a UTC value? In other words, the date I’m entering is “local time” and it will adjust depending on the user’s timezone setting.
  2. How do I set up a Date only field that does not act like a UTC value? Like a sales order’s date field where Jan 10, 2023 on the screen matches what is stored in the database, regardless of the user’s timezone setting?

 

icon

Best answer by Yuriy Zaletskyy 22 January 2023, 11:19

View original

1 reply

Userlevel 5
Badge +3

In Acumatica, a date field that is displayed in UTC is typically a field that is marked with the "PXDBDateAndTime" attribute. This attribute tells Acumatica to store the date in UTC format in the database, and to display it in the user's local time on the screen, taking into account the user's timezone setting.

On the other hand, a date field that is not displayed in UTC is typically a field that is marked with the "PXDBDate" attribute. This attribute tells Acumatica to store the date in the database without any time component and display it in the same format, regardless of the user's timezone setting.

To set up a date field that acts like a UTC value, you can add the "PXDBDateAndTime" attribute to the field in the DAC definition and use the "PXDBDateAndTime" attribute in the PXDBField attribute of the BQL Select statement.

To set up a date only field that does not act like a UTC value, you can add the "PXDBDate" attribute to the field in the DAC definition and use the "PXDBDate" attribute in the PXDBField attribute of the BQL Select statement.

Note: Acumatica also has a "PXDBDateTime" attribute that can be used in cases where the date and time is stored in UTC format in the database, but it is displayed on the screen in the user's local time without taking into account the user's timezone setting.

In order to manage, weather to use UTC, or not, take a look on UseTimeZone:

		/// <summary>Gets or sets the value that indicates whether the attribute
/// should convert the time to UTC, using the local time zone. If
/// <tt>true</tt>, the time is converted. By default,
/// <tt>true</tt>.</summary>
public virtual bool UseTimeZone
{
get { return _useTimeZone; }
set { _useTimeZone = value; }
}

Below goes sample of usage:


#region LastSettlementDateUTC
[PXDate(UseTimeZone = true)]
[PXUIField(DisplayName = "Last Settlement Date UTC")]
public virtual DateTime? LastSettlementDateUTC { get; set; }
public abstract class lastSettlementDateUTC : PX.Data.BQL.BqlDateTime.Field<lastSettlementDateUTC> { }
#endregion

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved