What determines the timezone for the SysTime on PartTran?

Kinetic ERP
1

I’m trying to figure out what time a transaction in PartTran actually occurred. I understand that SysTime is the seconds since midnight. However what dictates which timezone it’s using when calculating seconds since midnight? On quick glance it seems to not be adjust for timezone at all as I see 2 transactions for 2 plants that are in different timezones very close to each other in SysTime. But then I don’t know how you would ever know the actual time. Any help is appreciated, timezones make me want to pull out all my hair.

2

I believe all SysTime fields are managed by SQL and are based on the SQL Server’s clock and time zone.

1 Like
3

If you’re doing this in SQL this one’s easy to deal with.

cast(
	dateadd(
		second
		, PartTran.SysTime --< seconds since midnight
		, cast(PartTran.SysDate as datetime) --< can't add seconds to a date type
	) at time zone Plant.TimeZoneID --< didn't used to be able to apply a field like this!
	as datetime --< only if you're in BAQ, it doesn't believe in datetimeoffset type
)

Your skepticism is right though. Reality check all dates and times. Sometimes they write the client time, or the site time, or the server time, or it’s an offset that is minutes since 1953-10-30.

2 Likes
4

Sadly I don’t believe this to be the case. I did some more checking and it seems to use the Companies timezone? I wish it was server time, that would make my live so much easier.

5

It seems that upon further testing the Plant.TimeZoneId field is ignored and the SysCompany.TimeZoneId field is used for all PartTran SysDate/SysTime. I don’t really understand why this is but I was able to get the systime to change by flipping between different timezones on the company.

6

If you make a throwaway BAQ with a calculated nvarchar field with current_timezone() as the return value, does that match the timezone you’re expecting?

7

current_timezone() seems to return the server timezone. I also find it odd that it seems to have hardcoded UTC-6:00 into the return value for current_timezone() as if Central Time doesn’t flip flop between UTC-6 and UTC-5 depending on DST. Currently we’re on CDT which is UTC-5, but the return value doesn’t seem to be dynamic.
image

8

Yeah, that’s a part of the timezone ID, it’s not great that a static “offset” text is part of it. If you select current_timestamp you should get the correct time for that timezone at the moment.

It takes a little detective work but then it’s not hard to do some at time zone conversions and such to get things nailed down. I really wish the database development was kept current enough to use a datetimeoffset type instead of a date plus an int plus figuring out what time zone it represents, but such is life.

1 Like
9

I am an Epicor Kinetic Cloud customer.
I know this thread is 4 months old
This has not been tested or validated to be accurate.
I currently have the following in a BAQ (pulled from Query Phrase)

(DATEADD(SECOND, PartTran.SysTime, CAST(PartTran.SysDate AS DateTime))) as [Calculated_SysTimeStamp], 
	(SysTimeStamp AT TIME ZONE 'Central Standard Time' AT TIME ZONE Plant_TZ.Plant_TimeZoneID) as [Calculated_PlantTimeStamp],

Which amounts to:

DATEADD(
    SECOND
    , PartTran.SysTime  -- < seconds since midnight
    , CAST(PartTran.SysDate AS DateTime)  -- < can't add seconds to a date type
    )  AT TIME ZONE 'Central Standard Time' -- < local data center time
   AT TIME ZONE Plant.TimeZoneID -- < Plant Time Zone

YMMV

How to convert server timezone to local timezone for SQL server database (smarterasp.net)

10

There are multiple places where timezones can be stored so I think it is going to depend. I am going to put some of the field help comments here…

In Site configuration you have timezone offset
“The number of hours (±) difference from the server time zone to the Site time zone. Data collection transactions will be offset by this amount.” This is ignored if there is a timezone set in Site Maintenance

In Site Maintenance you have timezone
“Time Zone of the site.”

In company maintenance you Timezone
Description is super helpful with “TimeZoneID” This overrides the Server TimeZone

Then finally in system agent schedules you have Timezone
Description again is super helpful with “TimeZoneID” I beleive all this does is make sure the schedules execute at the expected time in the give timezone and if empty defualts to the company timezone and if that’s empty uses the server TZ

And of course you have the timezone the server is running in.

Interestingly doing a quick test setting the TZ on in site maintenance to be 30 minutes to my server and doing a quantity adjustment makes no difference. I even recycled the app server… Company TZ is empty.

May need to do some more exhaustive testing.

1 Like