Error on the time if an event

[new_otm]

Hi,
I have created an event fora shipment but the event history time is two hours more than the time saved on date base on table ie_status shipment.
I am in Spain and the location i have chosen for the event is in Spain.
Does anyone how to solve this?
Thanks.

[rvhaaster]

Re: Error on the time if an event

New_otm,

Times in db are stored as UTC whereas the eventtime as displayed in eventhistory is displayed taking into account the timezone of the location for wich the event was created. As Spain is either 1 or 2 hours diff from UTC (depending on daylight saving time), this explains the diff between screen and db value.
All times in OTM are stored in DB for UTC.

[new_otm]

Re: Error on the time if an event

Hi,
So, it is possible to change the database configuration?

Thanks.

[rvhaaster]

Re: Error on the time if an event

Why would you as this is standard & correct behaviour?

[new_otm]

Re: Error on the time if an event

Hi,
I am trying to make a custom report with a query and i want to show the event date field. I am getting it from ie_shipmentstatus table and it is making me confuse because i am seeing on the screen one time for example 17:00 and on database i am getting two hours before for example 15:00 . On screen I always see event date on CET time zone so I want to get in my query the same datetime.
Thanks.

[rvhaaster]

Re: Error on the time if an event

Hi,

You could consider using the function UTC.GET_LOCAL_DATE(event_date,location)

[new_otm]

Re: Error on the time if an event

Hi,

I am not able to use UTC.get_local_date fuction, i don't know what i should put on the location field.

The error i am getting is ora.20000=Undefined time zone for location

Thanks.

[rvhaaster]

Re: Error on the time if an event

Have you tried something like utc.get_local_date(ie_shipmentstatus.EVENTDATE,ss_ status_history.EVENT_LOCATION_GID) ?

By doing so I assume you're events are reported at shipment stop (i.e. linked to location_gid) and not at shipment_position. If at shipment position I think the following fields will be null:
ie_shipmentstatus.time_zone_gid
ss_status_history.event_location_gid
and I would not know how to convert UTC to time zone in spain unless by hardcoded delta.

If you need more help I hope some of the tech people out there can help you a bit more, as for me it stops here.

Good luck!

[new_otm]

Re: Error on the time if an event

Hi,
That's what i jave tried an i get the error.
Thank ypu very much for your help