dealing with time across national boundaries

.

    As more of my databases are used in foreign countries for trading, an accurate recording of time becomes important. Some trades only last for a few hours. I have two ways of recording time.

    When a row is added to a table I fill in a "posted_date" column using sysdate from the server here in CT. Users never even see the column.

    But users do fill in their own time columns. For example they post an expiry date/time for a row (a trade). Of course that goes in as whatever time their desktop machine is set to. As you know anyone can mess around with their desktop computer and set the date and time and the display format to whatever they wish. Also, even if a user has his machine set properly, the expiry time he fills in would be the local time at his location. If he's sitting in Singapore, then he's using Singapore time.

    To complicate things more, at any location time often does not vary according to any fixed format. For example in the US we might go on daylight savings time the third Sunday in March at 2:00 in the morning. Or we might not, if there is an energy crisis and Congress sets a different day that year. And three states don't observe daylight savings time at all. In Europe the countries each set their own start and stop day for daylight savings time if they use it at all.

    This means that even if I try to enter times consistently as some standard, say Greenwich Mean Time, I will have a hard time knowing the offset from GMT for any particular location and day.

    I am sure that large multinationals have solved this and I'm sure that SAP has a methodology for its international customers. Do you know what it is or where I can read more about it? Tom Kyte suggests storing both GMT and local time. Is that the best solution?

Van Received on Sat Dec 30 2000 - 10:14:37 CST