Error on the time if an event

new_otm · #1 (**permalink**) April 2nd, 2008, 12:26

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 · #2 (**permalink**) April 2nd, 2008, 13:01

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 · #3 (**permalink**) April 2nd, 2008, 15:01

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

Thanks.

rvhaaster · #4 (**permalink**) April 2nd, 2008, 15:05

Why would you as this is standard & correct behaviour?

new_otm · #5 (**permalink**) April 2nd, 2008, 15:41

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 · #6 (**permalink**) April 2nd, 2008, 16:04

Hi,

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

new_otm · #7 (**permalink**) April 2nd, 2008, 17:14

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 · #8 (**permalink**) April 2nd, 2008, 17:29

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 · #9 (**permalink**) April 2nd, 2008, 17:43

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