Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for time zones [CORE694] #1061

Closed
firebird-automations opened this issue Sep 17, 2003 · 19 comments
Closed

Support for time zones [CORE694] #1061

firebird-automations opened this issue Sep 17, 2003 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: Stefan Heymann (stefanheymann)

This issue seems to be the same as CORE909.

@firebird-automations
Copy link
Collaborator Author

Commented by: Thomas Braun (cew3)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @kattunga

.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Support timezones with timestamp => Support for time zones

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants