Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 16/05/2018 11:11, Mark Rotteveel wrote:
>
> If you look at PostgreSQL, they release a new point release with
> updated time zone data. I don't see why we need to make this more
> complicated than that.
>
> However that does mean, that we need to have a quick turn-around time
> for new releases (especially given the annoying habit of some
> countries of making changes like this on short notice). If we can't do
> that, we will need to provide some way to make update the time zone
> data easily(!). No hacks or requiring users to recompile ICU or
> anything like that.
>

Sure, every point release should come with the most updated data, but
additional package with updated data should be available too.

We'd better have an automatically build and upload script doing this.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Mark Rotteveel]

On 14-5-2018 09:13, Simonov Denis via Firebird-devel wrote:
Adriano dos Santos Fernandes  wrote Thu, 10 May 
2018 18:21:49 +0300:



Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?

Adriano



I'm wondering how the time zones will be updated?
Will they be distributed as a separate file in Firebird?
Can I register a new rule for time zones without updating Firebird and 
the backup/restore process?

If so, is there a DLL syntax for registering a new time zone rule?


If you look at PostgreSQL, they release a new point release with updated 
time zone data. I don't see why we need to make this more complicated 
than that.


However that does mean, that we need to have a quick turn-around time 
for new releases (especially given the annoying habit of some countries 
of making changes like this on short notice). If we can't do that, we 
will need to provide some way to make update the time zone data 
easily(!). No hacks or requiring users to recompile ICU or anything like 
that.


Mark
--
Mark Rotteveel

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 15/05/18 10:18, Alex Peshkoff via Firebird-devel wrote:

  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?



Can it be instead hardcoded in the function(s), describing MSK timezone? 
I ask cause building a system that describes well former whims of our 
government appears useless - I believe they can invent something else in 
the future which will be out of scope of system built to describe 
previous solutions :-) But if we have no other way to describe MSK 
timezone (i.e. with random changes of offset at given moments of time) - 
in that case this will be required.


All post 1970 changes to offset's are logged via the TZ database and the 
Moscow time changes are based on the 'Russia' rule set ( which is used 
by several timezones! ) up until the 2014 change when Europe/Moscow 
diverges from the generic rule set. Many time zones use one of a sub set 
of generic rules where they are associated with different locations. MSK 
is actually used in several timezone rule sets.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Alex Peshkoff via Firebird-devel]

On 05/10/18 19:40, Vlad Khorsun via Firebird-devel wrote:

10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


   I guess the question is about the case when users have historical 
data

and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?



Can it be instead hardcoded in the function(s), describing MSK timezone? 
I ask cause building a system that describes well former whims of our 
government appears useless - I believe they can invent something else in 
the future which will be out of scope of system built to describe 
previous solutions :-) But if we have no other way to describe MSK 
timezone (i.e. with random changes of offset at given moments of time) - 
in that case this will be required.




--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


   I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID  NameValid from  Offset
... MSK 2011.03.27 02:00+4
... MSK 2014.10.26 02:00+3

Is it makes sence ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 14/05/2018 10:24, Dimitry Sibiryakov wrote:
>
>   It doesn't need to be DDL. Stored procedures in timezone package
> should be enough.
>
>   What are packages for if even developers don't want to use them?..
>
>
If by developers you mean core team, for example:

- Do not improve the hell of not having a system schema.

- Some others limitations that system functions, for example, no
overloads (or variant type) to accept same function name with different
argument types.

- Do not do same thins that were being doing in another way, in a new way.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

14.05.2018 14:55, Vlad Khorsun via Firebird-devel wrote:
Can I register a new rule for time zones without updating Firebird and the 
backup/restore process?

If so, is there a DLL syntax for registering a new time zone rule?


   This is interesting and valid question (as for me). With such possibility
Lester could add missed historical rules, i think.


  It doesn't need to be DDL. Stored procedures in timezone package should be 
enough.

  What are packages for if even developers don't want to use them?..


--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 14/05/18 14:02, Adriano dos Santos Fernandes wrote:

So he updates original db and ICU and have what he wants.
That only works if TZ is installed using the backzone data, and as you 
have seen this is not the case. The real world solution for this IS 
TZDist ... and you would simply patch any locally cached data from that.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 14/05/18 13:55, Vlad Khorsun via Firebird-devel wrote:
Can I register a new rule for time zones without updating Firebird and 
the backup/restore process?

If so, is there a DLL syntax for registering a new time zone rule?


   This is interesting and valid question (as for me). With such 
possibility

Lester could add missed historical rules, i think.


One of the items on my back burner is a Firebird powered TZDist service 
which would provide versioned sets of rules and the check facility for 
individual timezones. The data would simply be a Firebird database ...
The problem as always is lack of time due to trying to play catchup with 
all the other non-BC changes being made to the whole infrastructure!


I'm documenting the notes on this service on the http://timedb.co.uk 
site which is where it is intended to host it. But local copies of the 
data would allow off-line use and TSDist would then provide patch 
updates to that data without needing to retransmit the whole TZ database 
every time!


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 14/05/2018 09:30, Lester Caine wrote:
> On 14/05/18 13:22, Adriano dos Santos Fernandes wrote:
>>> Can I register a new rule for time zones without updating Firebird and
>>> the backup/restore process?
>>> If so, is there a DLL syntax for registering a new time zone rule?
>>>
>> Serious, you want resgister custom time zones rules?
>
> I read that simply as if TZ add new timezones how are the ones hard
> coded in Firebird updated ...
>
As I said previously, this can be flexibilized and put in a file.

The time zone list inside Firebird code is only to have fixed IDs for
each time zone string.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 14/05/2018 09:55, Vlad Khorsun via Firebird-devel wrote:
>
>> Can I register a new rule for time zones without updating Firebird
>> and the backup/restore process?
>> If so, is there a DLL syntax for registering a new time zone rule?
>
>   This is interesting and valid question (as for me). With such
> possibility
> Lester could add missed historical rules, i think.
>
And I see no problem for him, if this is real world issue, I'm certainly
that anyone even did tools to help do it with the original or the ICU
use of the original db.

So he updates original db and ICU and have what he wants.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 14/05/18 13:22, Adriano dos Santos Fernandes wrote:

Can I register a new rule for time zones without updating Firebird and
the backup/restore process?
If so, is there a DLL syntax for registering a new time zone rule?


Serious, you want resgister custom time zones rules?


I read that simply as if TZ add new timezones how are the ones hard 
coded in Firebird updated ...


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Simonov Denis via Firebird-devel]

Adriano dos Santos Fernandes  wrote Thu, 10 May 2018  
18:21:49 +0300:



Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?

Adriano



I'm wondering how the time zones will be updated?
Will they be distributed as a separate file in Firebird?
Can I register a new rule for time zones without updating Firebird and the  
backup/restore process?

If so, is there a DLL syntax for registering a new time zone rule?


--
Simonov Denis


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

11.05.2018 13:22, Adriano dos Santos Fernandes wrote:

I do not thought about it, but would be very good to put them in another
virtual table if possible.


  May be a package with a set of procedures would be better then?..


--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 11/05/2018 01:20, Vlad Khorsun via Firebird-devel wrote:
>
>>
>> Every range (x-y) must define a displacement.
>
>   Good, now it is much more clear (for me, at least).
> It there a way to query\extract this ranges and coresponding
> displacements ?
>
I do not thought about it, but would be very good to put them in another
virtual table if possible.

Will check it.


Adriano
 

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

11.05.2018 1:27, Adriano dos Santos Fernandes wrote:

On 10/05/2018 18:03, Vlad Khorsun via Firebird-devel wrote:

...

Could you answer my question below ?

   Please, convert to UTC and explain how to do it:

    2010.03.01 04:00 Europe/Moscow,
    2013.03.01 04:00 Europe/Moscow,
    2016.03.01 04:00 Europe/Moscow



-
SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' t1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' t2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' t3
CON>   from rdb$database;

T1  2010-03-01 04:00:00. Europe/Moscow
T2  2013-03-01 04:00:00. Europe/Moscow
T3  2016-03-01 04:00:00. Europe/Moscow


SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' at time zone 'GMT' x1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' at time zone 'GMT' x2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' at time zone 'GMT' x3
CON>   from rdb$database;

X1  2010-03-01 01:00:00. GMT
X2  2013-03-01 00:00:00. GMT
X3  2016-03-01 01:00:00. GMT


SQL> select
CON> extract(timezone_hour from timestamp '2010-03-01 04:00
Europe/Moscow') tzh1,
CON> extract(timezone_hour from timestamp '2013-03-01 04:00
Europe/Moscow') tzh2,
CON> extract(timezone_hour from timestamp '2016-03-01 04:00
Europe/Moscow') tzh3
CON>   from rdb$database;

TZH13
TZH24
TZH33
-


  Very good, thanks !


Here I show interesting example when DST starts.

Two different GMT dates apparently maps to the same America/Sao_Paulo.

But if you inspect they timezone displacements, they are different.

-
SQL> select
CON> timestamp '2018-02-18 01:00 GMT' at time zone
'America/Sao_Paulo' t1,
CON> timestamp '2018-02-18 02:00 GMT' at time zone
'America/Sao_Paulo' t2
CON>   from rdb$database;

T1  2018-02-17 23:00:00. America/Sao_Paulo
T2  2018-02-17 23:00:00. America/Sao_Paulo


SQL> select
CON> extract(timezone_hour from timestamp '2018-02-18 01:00 GMT' at
time zone 'America/Sao_Paulo') tzh1,
CON> extract(timezone_hour from timestamp '2018-02-18 02:00 GMT' at
time zone 'America/Sao_Paulo') tzh2
CON>   from rdb$database;

TZH1-2
TZH2-3
-



Regards,
Vlad

PS It seems i start to understand you - you want to say that rule for
given area (full TZ name ?) describes all possible moments when offset
from UTC is changed. Including DST start\end and changes due to political
reasons. Correct ?



Yes, let's say that rule is defined from UTC to the time zone, and it
say, between UTC moment x and y, tz has a displacement of +/- hh:mm.

Every range (x-y) must define a displacement.


  Good, now it is much more clear (for me, at least).
It there a way to query\extract this ranges and coresponding displacements ?

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 19:27, Adriano dos Santos Fernandes wrote:
> Here I show interesting example when DST starts.

Correction: when DST *ends*.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 18:03, Vlad Khorsun via Firebird-devel wrote:

> 
>   To do it, you need to know TZ (or, offset from UTC) used at
> America/Sao_Paulo
> at given moment, isn't is ? And this offset is also depends on given moment
> itself. And in different moments there could be different offsets.
> Correct ?
> 

Yes.


> Could you answer my question below ?
> 
>   Please, convert to UTC and explain how to do it:
> 
>    2010.03.01 04:00 Europe/Moscow,
>    2013.03.01 04:00 Europe/Moscow,
>    2016.03.01 04:00 Europe/Moscow
> 

-
SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' t1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' t2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' t3
CON>   from rdb$database;

T1  2010-03-01 04:00:00. Europe/Moscow
T2  2013-03-01 04:00:00. Europe/Moscow
T3  2016-03-01 04:00:00. Europe/Moscow


SQL> select
CON> timestamp '2010-03-01 04:00 Europe/Moscow' at time zone 'GMT' x1,
CON> timestamp '2013-03-01 04:00 Europe/Moscow' at time zone 'GMT' x2,
CON> timestamp '2016-03-01 04:00 Europe/Moscow' at time zone 'GMT' x3
CON>   from rdb$database;

X1  2010-03-01 01:00:00. GMT
X2  2013-03-01 00:00:00. GMT
X3  2016-03-01 01:00:00. GMT


SQL> select
CON> extract(timezone_hour from timestamp '2010-03-01 04:00
Europe/Moscow') tzh1,
CON> extract(timezone_hour from timestamp '2013-03-01 04:00
Europe/Moscow') tzh2,
CON> extract(timezone_hour from timestamp '2016-03-01 04:00
Europe/Moscow') tzh3
CON>   from rdb$database;

TZH13
TZH24
TZH33
-


Here I show interesting example when DST starts.

Two different GMT dates apparently maps to the same America/Sao_Paulo.

But if you inspect they timezone displacements, they are different.

-
SQL> select
CON> timestamp '2018-02-18 01:00 GMT' at time zone
'America/Sao_Paulo' t1,
CON> timestamp '2018-02-18 02:00 GMT' at time zone
'America/Sao_Paulo' t2
CON>   from rdb$database;

T1  2018-02-17 23:00:00. America/Sao_Paulo
T2  2018-02-17 23:00:00. America/Sao_Paulo


SQL> select
CON> extract(timezone_hour from timestamp '2018-02-18 01:00 GMT' at
time zone 'America/Sao_Paulo') tzh1,
CON> extract(timezone_hour from timestamp '2018-02-18 02:00 GMT' at
time zone 'America/Sao_Paulo') tzh2
CON>   from rdb$database;

TZH1-2
TZH2-3
-


> Regards,
> Vlad
> 
> PS It seems i start to understand you - you want to say that rule for
> given area (full TZ name ?) describes all possible moments when offset
> from UTC is changed. Including DST start\end and changes due to political
> reasons. Correct ?
> 

Yes, let's say that rule is defined from UTC to the time zone, and it
say, between UTC moment x and y, tz has a displacement of +/- hh:mm.

Every range (x-y) must define a displacement.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 22:30, Leyne, Sean wrote:

No other database engine is maintaining various versions at the same

time.

Fortunately.

I believe that Oracle is.


I already put here link describing that when tz db is updated, times may
change if used in wrong version.

I think I misunderstood which "version" you were referring to.


Oracle uses two external files of timezone data. If they use the larger 
format file then they can't transfer that data to a system using the 
smaller format file. The smaller file has only a subset of timezone ID's 
and even the large one is missing valid backzone id's and even some main 
list entries!


The versioning only seems to affect the format of the iles, not the content.

--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Leyne, Sean]

> >> No other database engine is maintaining various versions at the same
> time.
> >> Fortunately.
> >
> > I believe that Oracle is.
> >
> 
> How?
> 
> I already put here link describing that when tz db is updated, times may
> change if used in wrong version.

I think I misunderstood which "version" you were referring to.



--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 18:22, Leyne, Sean wrote:
> 
> 
>> No other database engine is maintaining various versions at the same time.
>> Fortunately.
> 
> I believe that Oracle is.
> 

How?

I already put here link describing that when tz db is updated, times may
change if used in wrong version.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Leyne, Sean]

> No other database engine is maintaining various versions at the same time.
> Fortunately.

I believe that Oracle is.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 22:08, Adriano dos Santos Fernandes wrote:

On 10/05/2018 15:30, Vlad Khorsun via Firebird-devel wrote:


   The key is that DST doesn't affect given time zone. TZ could be
"standard time" based (such as MSK)
or "DST based" (such as MSD or IST you speak about) but TZ itself is
not changed with DST start\end.
What is changed - is what TZ should be used. But we (I) speak not
about it.


That only in this scheme you linked, that is un-mappable to computers.

This scheme also has invalid date/tz.

To convert a timestamp to UTC I need to known the timestamp and its
timezone.

So, I can't convert 2018-05-01 00:00 BRST (Brazilian Summer Time) to
UTC, as there is no summer time in 2018-05-01, only standard time.

And 2018-01-01 00:00 BRST + 4 months will not change to BRT (Brazilian
Standard Time).

This scheme just does not work!

On the other hand, I can convert any timestamp in America/Sao_Paulo to UTC.


  To do it, you need to know TZ (or, offset from UTC) used at America/Sao_Paulo
at given moment, isn't is ? And this offset is also depends on given moment
itself. And in different moments there could be different offsets.
Correct ?


I can add any interval to any America/Sao_Paulo timestamp and it will be
already an America/Sao_Paulo timestamp.

In fact the arithmetic is very simple, just add the interval to the
stored UTC timestamp.

When converting back to a string representation, it will be showed in
America/Sao_Paulo time.


  It is very good (while have no answer on "versioned" time zones question).
Could you answer my question below ?

  Please, convert to UTC and explain how to do it:

   2010.03.01 04:00 Europe/Moscow,
   2013.03.01 04:00 Europe/Moscow,
   2016.03.01 04:00 Europe/Moscow

Regards,
Vlad

PS It seems i start to understand you - you want to say that rule for
given area (full TZ name ?) describes all possible moments when offset
from UTC is changed. Including DST start\end and changes due to political
reasons. Correct ?

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 15:30, Vlad Khorsun via Firebird-devel wrote:
>
>   The key is that DST doesn't affect given time zone. TZ could be
> "standard time" based (such as MSK)
> or "DST based" (such as MSD or IST you speak about) but TZ itself is
> not changed with DST start\end.
> What is changed - is what TZ should be used. But we (I) speak not
> about it.
>
That only in this scheme you linked, that is un-mappable to computers.

This scheme also has invalid date/tz.

To convert a timestamp to UTC I need to known the timestamp and its
timezone.

So, I can't convert 2018-05-01 00:00 BRST (Brazilian Summer Time) to
UTC, as there is no summer time in 2018-05-01, only standard time.

And 2018-01-01 00:00 BRST + 4 months will not change to BRT (Brazilian
Standard Time).

This scheme just does not work!

On the other hand, I can convert any timestamp in America/Sao_Paulo to UTC.

I can add any interval to any America/Sao_Paulo timestamp and it will be
already an America/Sao_Paulo timestamp.

In fact the arithmetic is very simple, just add the interval to the
stored UTC timestamp.

When converting back to a string representation, it will be showed in
America/Sao_Paulo time.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 15:31, Lester Caine wrote:
> On 10/05/18 18:47, Adriano dos Santos Fernandes wrote:
>> 99.9% of our users will use the "official" database.
>
> And therefore will have no idea just which "official" database they
> are using. This is just carrying on the current mess that is inherent
> when one has no idea just which rules WERE USED to create the data. I
> became involved with the TZ list because a large block of genealogical
> data I was using was giving conflicting offsets. Over time the
> historic rules have been change in much the same way the current rules
> are change. North Korea has just added a rule change but it has still
> to filter through all the "official" databases so we are all using the
> new rule, but one needs to know that the UTC time logged two weeks ago
> for the meeting next week HAS changed! The VERSION of the rule set
> being used is just as important as the timezone and in a database with
> many years of data the rules WILL be changing so one needs to know
> just WHICH rule was applied when the data was stored against now. The
> material I was working with was essentially trash as no one had
> recorded the version of TZ that was being used, and as different
> computer OS's used different version of TZ the whole thing became a mess.
>
> Today the CORRECT identification of a timezone rule set is 'tz
> ident/version' and recording anything less is just a gamble on what
> you get.
>
> I can give more examples of problems which NOT knowing which rule set
> was used to create the 'international' timetable when local DST times
> are changed at short notice ... TZDist is intended to prevent this
> problem, but since there are no active sources we are still stuck with
> the miss-match of data between different OS's and system.
>
if you want to work with fixed rules even when they change, you should
have specialized treatment of the data. It's not difficult.

Store your data using the displacement syntax +/- HH:MM and add the
region name to another column.

No other database engine is maintaining various versions at the same
time. Fortunately.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 19:30, Vlad Khorsun via Firebird-devel wrote:
   The key is that DST doesn't affect given time zone. TZ could be 
"standard time" based (such as MSK)
or "DST based" (such as MSD or IST you speak about) but TZ itself is not 
changed with DST start\end.

What is changed - is what TZ should be used. But we (I) speak not about it.


Officially a TZ 'timezone' defines unique set of rules for the OFFSET 
changes for that 'location' and under the current practice, any timezone 
that has the same rule set since 1970 has been merged to reduce the 
number of identifiers used in TZ. Some of those rule sets DO diverge 
prior to 1970, so unless you have an OS that is using the backzone file 
the rule set used as a base will be used rather than the actual changes 
to the offset for the merged rule sets.


In many cases timezone is used - incorrectly - to describe the offset 
and not the set of rules plotting the changes to the offset.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 21:21, Adriano dos Santos Fernandes wrote:


The name for Moskow is Europe/Moscow.


  Please, convert to UTC and explain how to do it:

   2010.03.01 04:00 Europe/Moscow,
   2013.03.01 04:00 Europe/Moscow,
   2016.03.01 04:00 Europe/Moscow

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 18:47, Adriano dos Santos Fernandes wrote:

99.9% of our users will use the "official" database.


And therefore will have no idea just which "official" database they are 
using. This is just carrying on the current mess that is inherent when 
one has no idea just which rules WERE USED to create the data. I became 
involved with the TZ list because a large block of genealogical data I 
was using was giving conflicting offsets. Over time the historic rules 
have been change in much the same way the current rules are change. 
North Korea has just added a rule change but it has still to filter 
through all the "official" databases so we are all using the new rule, 
but one needs to know that the UTC time logged two weeks ago for the 
meeting next week HAS changed! The VERSION of the rule set being used is 
just as important as the timezone and in a database with many years of 
data the rules WILL be changing so one needs to know just WHICH rule was 
applied when the data was stored against now. The material I was working 
with was essentially trash as no one had recorded the version of TZ that 
was being used, and as different computer OS's used different version of 
TZ the whole thing became a mess.


Today the CORRECT identification of a timezone rule set is 'tz 
ident/version' and recording anything less is just a gamble on what you 
get.


I can give more examples of problems which NOT knowing which rule set 
was used to create the 'international' timetable when local DST times 
are changed at short notice ... TZDist is intended to prevent this 
problem, but since there are no active sources we are still stuck with 
the miss-match of data between different OS's and system.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 21:13, Lester Caine wrote:

On 10/05/18 18:47, Vlad Khorsun via Firebird-devel wrote:
Regions that use Daylight Saving Time (DST) change the time zone name and time during the DST period. The words “daylight” or 
“summer” are then usually included in the time zone name. The areas that don't use DST remain on standard time zone all year.


This view of things is somewhat out of date. There has been a discussion recently on the TZ list over 'Ireland' which uses 'standard 
time' in the summer, and changes from that in the winter ... hence a -ve DST offset rather than positive. Also DST in the southern 
hemisphere is reversed and Australian abbreviations don't follow the same guide. Some tools could not handle the -ve DST so it's 
implementation has been delayed, but the plan is that the correct data will be provided by TZ at least in future.




  The key is that DST doesn't affect given time zone. TZ could be "standard 
time" based (such as MSK)
or "DST based" (such as MSD or IST you speak about) but TZ itself is not 
changed with DST start\end.
What is changed - is what TZ should be used. But we (I) speak not about it.

Regads,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 14:47, Vlad Khorsun via Firebird-devel wrote:
>
>   According to:
>
>     https://www.timeanddate.com/time/time-zones.html
>
> ---
> Daylight Saving Time Zones
>
> Regions that use Daylight Saving Time (DST) change the time zone name
> and time during the DST period. The words “daylight” or “summer” are
> then usually included in the time zone name. The areas that don't use
> DST remain on standard time zone all year.
>
> For example, California uses Pacific Daylight Time (PDT) during the
> DST period, but Pacific Standard Time (PST) during the rest of the year.
> ---
>
>
And when you read more this page, you understand that this scheme does
not work for computers:

---
Local Time Zone Names
Illustration image
Mountain Standard Time or Mountain Daylight Time or just Mountain Time?
PunchStock.com
To confuse matters more, each time zone can have different local time
zone names, usually linked to the geographical name of the country or
region. The time zone names may be completely different, even though the
UTC offset is the same.

For instance in Miami, Florida, is 5 hours behind UTC (UTC-5) and the
standard time zone is Eastern Standard Time (EST). In Havana, Cuba, the
standard time zone is also UTC-5, but it's called Cuba Standard Time (CST).


Identical Abbreviations
Another point that can cause confusion is that some time zone's names in
totally different places have exactly the same abbreviation. For
example, India Standard Time (IST) and Israel Standard Time (IST) have
the same abbreviation, but completely different UTC offsets of UTC+5:30
and UTC+2:00.

In many parts of the world, especially in countries with only one time
zone, time zone names are not commonly used at all.
---

>
>> A DST is nothing different than a "rule change" and another "rule
>> change" every year.
>
>   Nope. DST doesn't affect time zone. It affects what time zone is actual
> at given region. Before 2011 it was MSK "at winter" and MSD "at
> summer" at
> Moscow area.
>
Brazil has various time zones but does not have various 3/4 letters
abbreviations for them.

But we can use then in computers (with ICU in this case) using the names:

America/Sao_Paulo
Brazil/Acre
Brazil/DeNoronha
...

ICU does not even define BRT/BRST. AFAIK these names are ambiguous.

The name for Moskow is Europe/Moscow.

See the list here:
https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/src/common/TimeZones.h


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 18:47, Vlad Khorsun via Firebird-devel wrote:
Regions that use Daylight Saving Time (DST) change the time zone name 
and time during the DST period. The words “daylight” or “summer” are 
then usually included in the time zone name. The areas that don't use 
DST remain on standard time zone all year.


This view of things is somewhat out of date. There has been a discussion 
recently on the TZ list over 'Ireland' which uses 'standard time' in the 
summer, and changes from that in the winter ... hence a -ve DST offset 
rather than positive. Also DST in the southern hemisphere is reversed 
and Australian abbreviations don't follow the same guide. Some tools 
could not handle the -ve DST so it's implementation has been delayed, 
but the plan is that the correct data will be provided by TZ at least in 
future.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 14:47, Dimitry Sibiryakov wrote:
> 10.05.2018 19:14, Adriano dos Santos Fernandes wrote:
>>>    IMHO, one of advantages of using UDR for subj is that much more
>>> fields can be added any time as needed by upgrading a single library
>>> while a virtual table is fixed to ODS and has to be decided once and
>>> forever.
>>>
>> UDR needs metadata too, so I don't see a difference in this respect.
>
>   Hmmm... When I heard that UDRs in contrast to UDFs are invulnerable
> to parameter redeclaration exploit, I decided that they provide
> metadata from inside and that's what IMessageMetadata is for. If not,
> what the purpose of IMessageMetadata that is returned from them?
>
>
Same as it is for the client.

Code declares the parameter as it needs it, independent of metadata. Of
course, this is optional, as in the client.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

10.05.2018 19:14, Adriano dos Santos Fernandes wrote:

   IMHO, one of advantages of using UDR for subj is that much more
fields can be added any time as needed by upgrading a single library
while a virtual table is fixed to ODS and has to be decided once and
forever.


UDR needs metadata too, so I don't see a difference in this respect.


  Hmmm... When I heard that UDRs in contrast to UDFs are invulnerable to parameter 
redeclaration exploit, I decided that they provide metadata from inside and that's what 
IMessageMetadata is for. If not, what the purpose of IMessageMetadata that is returned 
from them?



--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 13:32, Lester Caine wrote:
> This is exactly the problem, and using TZ data direct is the only
> reliable way to manage the CURRENT TZ rules. So as long as we can
> switch off ICU data then this may work, otherwise it is just another
> waste of time! In my case all the TZ material is managed via the PHP
> timezone classes and that uses the TZ data direct so there needs to be
> SOME management of just which rules Firebird is using 'automatically'?

There will be no time zone plugin, sorry.

But anyway you can edit the time zones database and recompile ICU if you
want a custom database.

99.9% of our users will use the "official" database.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 20:29, Adriano dos Santos Fernandes пишет:

On 10/05/2018 14:23, Vlad Khorsun via Firebird-devel wrote:



    Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?


America/Sao_Paulo changes every year because DST (when starting to -02
and ending to -03).


   The case is not about DST. It is about *change of rule* for particular
time zone. Does you read table at link above ?

If i understand it correct:

   2010.03.01 04:00 MSK == 2010.03.01 00:00 UTC,
   2013.03.01 04:00 MSK == 2013.03.01 00:00 UTC, but
   2016.03.01 04:00 MSK == 2016.03.01 01:00 UTC


In practical terms, it's the same as a DST change.


  According to:

https://www.timeanddate.com/time/time-zones.html

---
Daylight Saving Time Zones

Regions that use Daylight Saving Time (DST) change the time zone name and time during the DST period. The words “daylight” or 
“summer” are then usually included in the time zone name. The areas that don't use DST remain on standard time zone all year.


For example, California uses Pacific Daylight Time (PDT) during the DST period, but Pacific Standard Time (PST) during the rest of 
the year.

---

So, in Moscow:
2010.03.01 used MSK (UTC +3)
2010.03.28 DST start (MSK -> MSD)
2010.04.01 used MSD (UTC +4)
2010.10.31 DST end (MSD -> MSK)
2011.03.01 used MSK (UTC +3)
2011.03.27 used MSK (UTC +4)  <<<--- rule for MSK was changed !!!
... don't use DST, don't use MSD
2014.10.26 used MSK (UTC +3)  <<<--- rule for MSK was changed again !!!
... don't use DST, don't use MSD



A DST is nothing different than a "rule change" and another "rule
change" every year.


  Nope. DST doesn't affect time zone. It affects what time zone is actual
at given region. Before 2011 it was MSK "at winter" and MSD "at summer" at
Moscow area.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 17:24, Adriano dos Santos Fernandes wrote:

Would that name be the full name like Europe/Amsterdam, or would it
also include the imprecise (duplicates are possible) like CET?


Both, each one has a different ID.


There are multiple abbreviations that relate to different rules. Only 
the full name gives the individual rule. This is one of the areas that 
Paul has been modifying the use of abbreviations where in many cases TZ 
invented them in the first place. The main problem is however simple 
things like does your version of ICU include or exclude the 'backzone' 
file. TZ is only designed to be complete since 1970 so the use of TZ 
offsets and Daylight Saving information prior to 1970 is something of a 
lottery! It is another flag that needs to be available so that you know 
if the TZ rules supplied can be used with genealogical data for 
instance. 'backzone' contains a number of historic abbreviations some of 
which were inventions and should be ignored, and others are pre 1970 so 
not visible in some instances.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 14:29, Adriano dos Santos Fernandes wrote:
> In practical terms, it's the same as a DST change.
>
> A DST is nothing different than a "rule change" and another "rule
> change" every year.
>
>
Some time zones nomenclature are specific to the region's standard or
DST times, so they are (or almost) fixed, one may be -03 and another
-02, for example.

But that is not true for all.

Say America/Sao_Paulo, it has different displacement from UTC depending
on the timestamp.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 14:23, Vlad Khorsun via Firebird-devel wrote:
>
>>>    Look how MSK time zone was changed at 2014.10.26
>>>
>>> https://www.timeanddate.com/time/zone/russia/moscow
>>>
>>> I think about something like this:
>>>
>>> ID    Name    Valid from    Offset
>>> ...    MSK    2011.03.27 02:00    +4
>>> ...    MSK    2014.10.26 02:00    +3
>>>
>>> Is it makes sence ?
>>
>> America/Sao_Paulo changes every year because DST (when starting to -02
>> and ending to -03).
>
>   The case is not about DST. It is about *change of rule* for particular
> time zone. Does you read table at link above ?
>
> If i understand it correct:
>
>   2010.03.01 04:00 MSK == 2010.03.01 00:00 UTC,
>   2013.03.01 04:00 MSK == 2013.03.01 00:00 UTC, but
>   2016.03.01 04:00 MSK == 2016.03.01 01:00 UTC
>
In practical terms, it's the same as a DST change.

A DST is nothing different than a "rule change" and another "rule
change" every year.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 19:57, Adriano dos Santos Fernandes пишет:

On 10/05/2018 13:47, Vlad Khorsun via Firebird-devel wrote:

10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


    I guess the question is about the case when users have historical
data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

    Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


   Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID    Name    Valid from    Offset
...    MSK    2011.03.27 02:00    +4
...    MSK    2014.10.26 02:00    +3

Is it makes sence ?


America/Sao_Paulo changes every year because DST (when starting to -02
and ending to -03).


  The case is not about DST. It is about *change of rule* for particular
time zone. Does you read table at link above ?

If i understand it correct:

  2010.03.01 04:00 MSK == 2010.03.01 00:00 UTC,
  2013.03.01 04:00 MSK == 2013.03.01 00:00 UTC, but
  2016.03.01 04:00 MSK == 2016.03.01 01:00 UTC


Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 14:08, Dimitry Sibiryakov wrote:
> 10.05.2018 18:44, Vlad Khorsun via Firebird-devel wrote:
>>    Is it possible\make sence to add a datetime field with "valid from"
>> mark ? Or something like that, some kind of version mark.
>
>   IMHO, one of advantages of using UDR for subj is that much more
> fields can be added any time as needed by upgrading a single library
> while a virtual table is fixed to ODS and has to be decided once and
> forever.
>

UDR needs metadata too, so I don't see a difference in this respect.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

10.05.2018 18:44, Vlad Khorsun via Firebird-devel wrote:

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


  IMHO, one of advantages of using UDR for subj is that much more fields can be added any 
time as needed by upgrading a single library while a virtual table is fixed to ODS and has 
to be decided once and forever.



--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 19:23, Adriano dos Santos Fernandes wrote:

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:


   I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone
rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


I really don't understood what you mean.

Please give more practical example.


  Look how MSK time zone was changed at 2014.10.26

https://www.timeanddate.com/time/zone/russia/moscow

I think about something like this:

ID  NameValid from  Offset
... MSK 2011.03.27 02:00+4
... MSK 2014.10.26 02:00+3

Is it makes sence ?

Regards,
Vlad


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 19:02, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:53, Lester Caine wrote:

...

And how will the version of TZ data be handled! This is a key element
that has screwed normalized data in the past, and some way to manage
that normalized data needs to be included in the process.



If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.


  I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone rule
to the new data.

  Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Leyne, Sean]

> 10.05.2018 19:02, Adriano dos Santos Fernandes wrote:
> > On 10/05/2018 12:53, Lester Caine wrote:
> ...
> >> And how will the version of TZ data be handled! This is a key element
> >> that has screwed normalized data in the past, and some way to manage
> >> that normalized data needs to be included in the process.
> >>
> >
> > If you mean "normalize data" as the fact that people uses an outdated
> > time zone db, insert data in the database on the outdated time zone,
> > and later update the db, the way to normalize data is up to the user/admin.
> 
>I guess the question is about the case when users have historical data and
> need to apply old time zone rule to the old data and new timezone rule to
> the new data.
> 
>Is it possible\make sence to add a datetime field with "valid from"
> mark ? Or something like that, some kind of version mark.

IIRC, the Oracle time zone data (stored outside the engine) has 'effective 
date' context.


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 17:12, Vlad Khorsun via Firebird-devel wrote:

10.05.2018 19:02, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:53, Lester Caine wrote:

...

And how will the version of TZ data be handled! This is a key element
that has screwed normalized data in the past, and some way to manage
that normalized data needs to be included in the process.



If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.


   I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone rule
to the new data.

   Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.


This is exactly the problem, and using TZ data direct is the only 
reliable way to manage the CURRENT TZ rules. So as long as we can switch 
off ICU data then this may work, otherwise it is just another waste of 
time! In my case all the TZ material is managed via the PHP timezone 
classes and that uses the TZ data direct so there needs to be SOME 
management of just which rules Firebird is using 'automatically'?


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 13:22, Mark Rotteveel wrote:
>
>> NAME VARCHAR
>
> Would that name be the full name like Europe/Amsterdam, or would it
> also include the imprecise (duplicates are possible) like CET?
>

Both, each one has a different ID.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 13:12, Vlad Khorsun via Firebird-devel wrote:
>
>   I guess the question is about the case when users have historical data
> and need to apply old time zone rule to the old data and new timezone
> rule
> to the new data.
>
>   Is it possible\make sence to add a datetime field with "valid from"
> mark ? Or something like that, some kind of version mark.
>
I really don't understood what you mean.

Please give more practical example.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Mark Rotteveel]

On 10-5-2018 17:51, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:27, Alex Peshkoff via Firebird-devel wrote:

On 05/10/18 18:21, Adriano dos Santos Fernandes wrote:

Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?


Can you provide a small sample? What columns will it have, when new
row is added to it, may be something else important from your POV.


Note that data is for server (i.e., all databases will list the same
info) and it's read only.

Basic info:
ID NUMERIC
NAME VARCHAR


Would that name be the full name like Europe/Amsterdam, or would it also 
include the imprecise (duplicates are possible) like CET?


Mark
--
Mark Rotteveel

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Vlad Khorsun via Firebird-devel]

10.05.2018 19:02, Adriano dos Santos Fernandes wrote:

On 10/05/2018 12:53, Lester Caine wrote:

...

And how will the version of TZ data be handled! This is a key element
that has screwed normalized data in the past, and some way to manage
that normalized data needs to be included in the process.



If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.


  I guess the question is about the case when users have historical data
and need to apply old time zone rule to the old data and new timezone rule
to the new data.

  Is it possible\make sence to add a datetime field with "valid from"
mark ? Or something like that, some kind of version mark.

Regards,
Vlad

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 13:00, Dimitry Sibiryakov wrote:
> 10.05.2018 17:57, Adriano dos Santos Fernandes wrote:
>> May make sense, but I think we didn't defined what is the difference
>> between a virtual table and these things, specially that a view would
>> also accept update/delete that triggers an action, a thing currently
>> used in virtual tables.
>
>   The UDR can provide triggers for the view, right?
>
>

Yes, if the trigger definition is also created.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 12:53, Lester Caine wrote:
> On 10/05/18 16:27, Leyne, Sean wrote:
>> Q's: How will the data be defined?  In code or via some external
>> file?  If external, how will changes be applied/recognized to running
>> engine instance?
>
> And how will the version of TZ data be handled! This is a key element
> that has screwed normalized data in the past, and some way to manage
> that normalized data needs to be included in the process.
>

If you mean "normalize data" as the fact that people uses an outdated
time zone db, insert data in the database on the outdated time zone, and
later update the db, the way to normalize data is up to the user/admin.

For time zone updates, that most depends on the ICU discussion.

If full ICU is included in Windows, just update ICU.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

10.05.2018 17:57, Adriano dos Santos Fernandes wrote:

May make sense, but I think we didn't defined what is the difference
between a virtual table and these things, specially that a view would
also accept update/delete that triggers an action, a thing currently
used in virtual tables.


  The UDR can provide triggers for the view, right?


--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 12:39, Dimitry Sibiryakov wrote:
> 10.05.2018 17:21, Adriano dos Santos Fernandes wrote:
>> I want to create a virtual table that lists available time zones.
>
>   Why a virtual table instead of UDR or a view based on UDR? Using of
> UDR would provide more flexibility and guarantee that data are actual.
>
>
May make sense, but I think we didn't defined what is the difference
between a virtual table and these things, specially that a view would
also accept update/delete that triggers an action, a thing currently
used in virtual tables.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 12:27, Leyne, Sean wrote:
>
> Q's: How will the data be defined?  In code or via some external file?  If 
> external, how will changes be applied/recognized to running engine instance?
>
>
Currently the data (only mapping from Firebird ID to ICU time zone name)
is defined at the code (compile time) and loaded in the start.

It may be changed to a file later.


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Lester Caine]

On 10/05/18 16:27, Leyne, Sean wrote:

Q's: How will the data be defined?  In code or via some external file?  If 
external, how will changes be applied/recognized to running engine instance?


And how will the version of TZ data be handled! This is a key element 
that has screwed normalized data in the past, and some way to manage 
that normalized data needs to be included in the process.


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

On 10/05/2018 12:27, Alex Peshkoff via Firebird-devel wrote:
> On 05/10/18 18:21, Adriano dos Santos Fernandes wrote:
>> Hi!
>>
>> I want to create a virtual table that lists available time zones.
>>
>> For now there is RDB$ (non-virtual), MON$ (virtual, but all about
>> monitoring), SEC$ (security plugin).
>>
>> What prefix should TIME_ZONES have?
>
> Can you provide a small sample? What columns will it have, when new
> row is added to it, may be something else important from your POV.
>
Note that data is for server (i.e., all databases will list the same
info) and it's read only.

Basic info:
ID NUMERIC
NAME VARCHAR

May also have:
CURRENT_DISPLACEMENT (from UTC)


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dmitry Yemanov]

10.05.2018 18:28, Mark Rotteveel wrote:


I don't see any reason why a virtual table can't have a RDB$ prefix.


+1


Dmitry

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Dimitry Sibiryakov]

10.05.2018 17:21, Adriano dos Santos Fernandes wrote:

I want to create a virtual table that lists available time zones.


  Why a virtual table instead of UDR or a view based on UDR? Using of UDR would provide 
more flexibility and guarantee that data are actual.



--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Mark Rotteveel]

On 10-5-2018 17:21, Adriano dos Santos Fernandes wrote:

Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?


Where is schema support when you need it ;)

I don't see any reason why a virtual table can't have a RDB$ prefix. I 
don't think it should use the MON$ naming as it isn't a monitoring table.


Mark
--
Mark Rotteveel

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Alex Peshkoff via Firebird-devel]

On 05/10/18 18:21, Adriano dos Santos Fernandes wrote:

Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?


Can you provide a small sample? What columns will it have, when new row 
is added to it, may be something else important from your POV.




--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Virtual table for time zones

[Leyne, Sean]

Adriano,

> I want to create a virtual table that lists available time zones.
> 
> For now there is RDB$ (non-virtual), MON$ (virtual, but all about monitoring),
> SEC$ (security plugin).
> 
> What prefix should TIME_ZONES have?

My feeling is to use the RDB$ prefix.


Q's: How will the data be defined?  In code or via some external file?  If 
external, how will changes be applied/recognized to running engine instance?


Sean

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] Virtual table for time zones

[Adriano dos Santos Fernandes]

Hi!

I want to create a virtual table that lists available time zones.

For now there is RDB$ (non-virtual), MON$ (virtual, but all about
monitoring), SEC$ (security plugin).

What prefix should TIME_ZONES have?


Adriano


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel