Issue Details (XML | Word | Printable)

Key: CORE-694
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Pavel Cisar
Votes: 24
Watchers: 10
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Support timezones with timestamp

Created: 17/Sep/03 12:00 AM   Updated: 15/Oct/12 08:46 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Time Tracking:
Not Specified

SF_ID: 808006


 Description  « Hide
SFID: 808006#
Submitted By: pcisar

Hello developers,

I develop application for evidence products from
manufacture. Because I in timezone Europe/Prague
we have daylight saving time over summer.

Summer - GMT+2
Winter - GMT+1.

Because FB stores as timestamp only localtime, when
time go from daylight saving time (GMT+2) to (GMT+1)
hour from 2 to 3 o'clock is in localtime twice, once in
GMT+2 and once in GMT+1.

0:00 GMT+2 -> 1:00 GMT+2 -> 2:00 GMT+2 ->3:00
GMT+2 -> back to 2:00 GMT+1 -> 3:00 GMT+2, ...

And now, I can not select from database products from
2:00 to 3:00 GMT+2 and 2:00 to 3:00 GMT+1, because
in DB is only localtime without timezone and when i
select products, returned products is from both hours.

Problem solution is store time in DB in GMT (UTC) and
when storing or getting time from/to DB recompute to
client timezone.

When specifying time in SQL command, time zone may
be included, for example: '2002-10-12 11:00:30 GMT+2'
or '2002-10-12 11:00:30 +0200' or '2002-10-12 11:00:30
CEST' or without timezone, recompute from client
timezone or for simplify use server timezone.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Steffen Heil added a comment - 04/Nov/09 08:39 PM
For client parsed dates this would not even require any code change: Just specify explicitly that the time information on the wire is suppost to be interpreted as UTC.
For conversion from/to text, the timezone should be recognized (defaulting to the clients timezone) and antomatically converted to UTC.

Ann Harrison added a comment - 04/Nov/09 11:09 PM
Wrong approach. Instead add the standard SQL types of TIMESTAMP WITH TIMEZONE and TIME WITH TIME ZONE. Messing with existing data types just leads to unhappiness.

Michael Ludwig added a comment - 24/Apr/10 11:47 AM
MySQL (I know) has the following:

UNIX_TIMESTAMP() Return a UNIX timestamp
UTC_DATE()(v4.1.1) Return the current UTC date
UTC_TIME()(v4.1.1) Return the current UTC time
UTC_TIMESTAMP()(v4.1.1) Return the current UTC date and time

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

All of the above are *very* useful, and none is in SQL-99 as far as I know. All of the above return temporal data independent of any timezone configured on the server machine, and therefore independent of any random changes (like DST) configured in the timezone (zoneinfo on Linux, or Olson database). These functions allow the application developer to isolate his application from any OS timezone settings and changes.

I've found purely arithmetic timezone functionality (as defined in another standard) to be of limited usefulness:

[xsl] Timezone concept broken in XPath 2.0?
http://www.biglist.com/lists/lists.mulberrytech.com/xsl-list/archives/200811/msg00088.html

Stefan Heymann added a comment - 16/Jul/10 09:36 AM
This issue seems to be the same as CORE-909.

Thomas Braun added a comment - 17/Jul/10 08:36 PM
That's true. Is there a timeline to solve this issue?

Christian Pradelli added a comment - 07/Sep/10 04:45 PM
I think that the correct behavior should be:
Firebird should automatically store all date/times in UTC and each client connection should specify it own timezone.
dates/times should be converted/displayed using the client timezone
Timezone should be taken from client OS setting and could be changed in a global user variable.

Dmitry Yemanov added a comment - 07/Sep/10 06:04 PM
Christian, how do you suggest to provide backward compatibility in this case?

ohenri added a comment - 07/Sep/10 08:20 PM - edited
I disagree with Christian Pradelli,
and I agree to Ann Harrison.

At most it is a decision of an application what kind of date/time has to be used or presented.

So, use of data types like timestamp with/without timezone (where classical datetime = datetime without timezone) and clauses like "current_datetime at timezone xxx" seems to be the right way - from my pont of view.

Backward compatibility:

datetime = datetime without timezone
current_datetime = current_datetime at local timezone

Extensions:
UTC shall be a valid timezone

Thomas Braun added a comment - 07/Sep/11 06:54 AM
Will this issue be assigned to a developer and solved some days???

How many developers are working on firebird?
In a global used application UTC is a MUST HAVE!!
Without UTC support, firebird will loose acceptance.


Christian Waldmann added a comment - 14/Oct/12 01:25 PM - edited
Are there some plans to enhance the UTC support for DATE, TIME, and TIMESTAMP.

We normally store TIMESTAMP in UTC in databases. So UTC_NOW would already help to calculate the age of events.

It is very unhandy to still use an UDF to get time and date in UTC or lets do the application do all conversions.
And without an UDF, some selections and calculation of TIMESTAMP in UTC are not possible in stored procedures.

Remark:
On the way to implement access to current time in UTC as UDF I found the undocumented function getExactTimestampUTC() in fb_udf, implemented at least for Windows platforms. So I can go on ....