Support for time zones [CORE694]

[firebird-automations]

Submitted by: @pcisar

Votes: 35

SFID: 808006#⁠
Submitted By: pcisar

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.

Commits: 64eea10 7100879

[firebird-automations]

Modified by: Alice F. Bird (firebirds)

description: 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.

=>

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.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10718 ] => Firebird [ 15061 ]

[firebird-automations]

Commented by: Steffen Heil (steffen-heil)

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.

[firebird-automations]

Commented by: Ann Harrison (awharrison)

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.

[firebird-automations]

Commented by: Michael Ludwig (milu)

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

[firebird-automations]

Commented by: Stefan Heymann (stefanheymann)

This issue seems to be the same as CORE909.

[firebird-automations]

Commented by: Thomas Braun (cew3)

That's true. Is there a timeline to solve this issue?

[firebird-automations]

Commented by: @kattunga

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.

[firebird-automations]

Commented by: @dyemanov

Christian, how do you suggest to provide backward compatibility in this case?

[firebird-automations]

Commented by: ohenri (ohenri)

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

[firebird-automations]

Commented by: Thomas Braun (cew3)

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.

[firebird-automations]

Commented by: Christian Waldmann (swissknife)

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 ....

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Edited the original description for readability (poor formatting from case conversion from SF tracker)

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: 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.

=>

SFID: 808006#⁠
Submitted By: pcisar

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.

[firebird-automations]

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ]