Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
I notify that synapse have some tools for timezone in synautil unit like:
{:Return your timezone bias from UTC time in minutes.}
function TimeZoneBias: integer;
{:Return your timezone bias from UTC time in string representation like
+0200.}
function TimeZone: string;
I don't know that they are cross platform. So they are some ways to get time
offset but anyone know how to get city? OS like ubuntu or windows ask user
for time zone in installation process, so it could be possible to get this
value by calling some enviromnent variable?
Regards
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 21:48, José Mejuto wrote: Hello FPC-Pascal, Monday, July 25, 2011, 12:54:13 PM, you wrote: RO Correct. Would you happen to have some cross platform code readily RO available that spits out either: RO 1. Offset from UTC for a certain (historical or future) date/time RO 2. Timezone abbreviation suitable for inclusion in my XML export (don't RO know yet what formats Access/ADO.Net/Excel/whatever needs) RO ... never hurts to ask, does it ;) Maybe this could help: http://wiki.lazarus.freepascal.org/PascalTZ Thanks José, I'll have a look. Even if not used for this code, it looks interesting! ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 10:50, Ludo Brands wrote:
1. Is there any way of knowing what kind of timezone a
datetime value
should be in a FPC dataset? (I suppose not...)
No, there is no way. It's always supposed to be 'local time'.
That is also what most of the underlying databases do. They store everything
in UTC and convert to local time. Only for those that support timezoned
field types (fe. oracle timestamp_TZ) you can specify a timezone when
entering data. MySQL fe. doesn't have support for this.
Ludo
Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC
conversion.
Sensible developers on multi country Firebird db projects would probably
store convert date/time info to UTC in the database.
How about this choice:
TDateTimeStorageFormat = (UTC, Local {, StoredInDatabase});
//Treat date/time fields in datasets as stored in local time or UTC.
Defaults to local time.
{ TODO 7 -oAnyone -cNice to have : If we can support databases that
store timezone info with date/time, e.g. Oracle, add an option
StoredInDatabase }
TXMLXSDFormatSettings = class(TExportFormatSettings)
property DateTimeStorageFormat: TDateTimeStorageFormat
read FDateTimeStorageFormat write FDateTimeStorageFormat;
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 11:21, Reinier Olislagers wrote:
On 25-7-2011 10:50, Ludo Brands wrote:
1. Is there any way of knowing what kind of timezone a
datetime value
should be in a FPC dataset? (I suppose not...)
No, there is no way. It's always supposed to be 'local time'.
That is also what most of the underlying databases do. They store everything
in UTC and convert to local time. Only for those that support timezoned
field types (fe. oracle timestamp_TZ) you can specify a timezone when
entering data. MySQL fe. doesn't have support for this.
Ludo
Mmmm. For example, IIRC, Firebird just stores it as entered - no UTC
conversion.
Sensible developers on multi country Firebird db projects would probably
store convert date/time info to UTC in the database.
How about this choice:
TDateTimeStorageFormat = (UTC, Local {, StoredInDatabase});
//Treat date/time fields in datasets as stored in local time or UTC.
Defaults to local time.
{ TODO 7 -oAnyone -cNice to have : If we can support databases that
store timezone info with date/time, e.g. Oracle, add an option
StoredInDatabase }
TXMLXSDFormatSettings = class(TExportFormatSettings)
property DateTimeStorageFormat: TDateTimeStorageFormat
read FDateTimeStorageFormat write FDateTimeStorageFormat;
I realize having a local timezone option will result in a hornet's nest
of adjustments for Daylight Saving Time etc... I'll try and check to see
if it is possible to specify the timezone description instead of the
offset in the export datetime field.
If so, I'll try and get timezone description from the OS...
Thanks,
Reinier
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Ludo Brands wrote: 1. Is there any way of knowing what kind of timezone a datetime value should be in a FPC dataset? (I suppose not...) No, there is no way. It's always supposed to be 'local time'. That is also what most of the underlying databases do. They store everything in UTC and convert to local time. Only for those that support timezoned field types (fe. oracle timestamp_TZ) you can specify a timezone when entering data. MySQL fe. doesn't have support for this. Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 12:09, Mark Morgan Lloyd wrote: Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. Correct. Would you happen to have some cross platform code readily available that spits out either: 1. Offset from UTC for a certain (historical or future) date/time 2. Timezone abbreviation suitable for inclusion in my XML export (don't know yet what formats Access/ADO.Net/Excel/whatever needs) ... never hurts to ask, does it ;) Thanks, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Reinier Olislagers wrote: On 25-7-2011 12:09, Mark Morgan Lloyd wrote: Pretty much the same for PostgreSQL. I went through all this a few months ago, and while I didn't explore data entry I found that for data output I had to apply corrections myself although I could get properties of the current timezone from an internal table. In all cases the fundamental question is whether the OS has been installed with correct notification of where it's operating, and whether is has any mechanism to track what's going on if it's moved around. Correct. Would you happen to have some cross platform code readily available that spits out either: 1. Offset from UTC for a certain (historical or future) date/time 2. Timezone abbreviation suitable for inclusion in my XML export (don't know yet what formats Access/ADO.Net/Excel/whatever needs) ... never hurts to ask, does it ;) Indeed :-) Unfortunately not, since what I did was have a .ini-format configuration file specifying (the current system's) lat/long and basic timezone, and then used an SQL query to ask the database what it thought was going on from a standard table (i.e. whether DST is currently in operation). Using this it was trivial to plug a DST correction back into the code, and also to display the effective timezone name. I doubt any of this is portable since it makes a lot of assumptions about the server. I've made a point for the last few years of having all critical machines locked to GMT. I once found myself having to deal with three different timezones in a forensic problem, and once is quite enough. If GPS could be persuaded to cough up basic information about the operative timezone in a standardised form (i.e. you're in a country that uses WET) it would make things much easier. Or failing that if somebody had a comprehensive translation between lat/long and basic timezone info, i.e. this polygon is GMT, this polygon is WET and so on; unlike transition dates etc. this wouldn't change very often. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Mark Morgan Lloyd wrote: If GPS could be persuaded to cough up basic information about the operative timezone in a standardised form (i.e. you're in a country that uses WET) it would make things much easier. Or failing that if somebody had a comprehensive translation between lat/long and basic timezone info, i.e. this polygon is GMT, this polygon is WET and so on; unlike transition dates etc. this wouldn't change very often. Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
Reinier Olislagers schrieb: I realize having a local timezone option will result in a hornet's nest of adjustments for Daylight Saving Time etc... I'll try and check to see if it is possible to specify the timezone description instead of the offset in the export datetime field. If so, I'll try and get timezone description from the OS... Timezone information is not enough to get reliable dates. Dependend on where dates origin from they can be wrong (wrong clock on computer, wrong time zone on computer, file times extracted from archives not storing time zone). Also, when calculating UTC time on a local (computer) there is an ambiguity for times 2:00h to 3:00h when daylight saving is invoked in spring. A time of 2:30h can be before or after having switched back the clock but a program cannot determine which case applies from the computer clock alone. And even if dates are correctly stored in UTC it is not easy to reliably get back the local time. Daylight savings were changed in the past (and may also change in the future). If you have a date/time of 2001-05-01 18:00 UTC it is not easy to predict what local time it was in time zone x. You would need a history of all daylight saving algorithms of the past and the future for all time zones (or even countries). Having time zones added (or storing it in UTC) is better than not doing this but it's still far away from being correct. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 25-7-2011 18:23, Jürgen Hestermann wrote: Reinier Olislagers schrieb: ... If so, I'll try and get timezone description from the OS... Timezone information is not enough to get reliable dates. Dependend on where dates origin from they can be wrong (wrong clock on computer, wrong time zone on computer, file times extracted from archives not storing time zone). Also, when calculating UTC time on a local (computer) there is an ambiguity for times 2:00h to 3:00h when daylight saving is invoked in spring. A time of 2:30h can be before or after having switched back the clock but a program cannot determine which case applies from the computer clock alone. Yep, I know, depressing... And even if dates are correctly stored in UTC it is not easy to reliably get back the local time. Daylight savings were changed in the past (and may also change in the future). If you have a date/time of 2001-05-01 18:00 UTC it is not easy to predict what local time it was in time zone x. You would need a history of all daylight saving algorithms of the past and the future for all time zones (or even countries). Agreed. Having time zones added (or storing it in UTC) is better than not doing this but it's still far away from being correct. Fortunately Ludo Brands showed me that storing timezone info in the XML file I was exporting (for ADO.Net) was not necessary. I just tested it as well... I suppose it's best left to the user to interpret what a date/time means exactly, just as he will have to do with currency fields without any currency indicator... However, what I can do is add an XML comment indicating what offset/timezone the computer is currently using. This may help interpretation... Thanks for the detailed explanation, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On Mon, Jul 25, 2011 at 11:53 AM, Mark Morgan Lloyd markmll.fpc-pas...@telemetry.co.uk wrote: Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. Location may be off too. All of the online location reporting tool lists my work machine as being in TX, even though I'm in TN, since all our networks are routed back to the home office before hitting the cloud. Jeff. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
DaWorm wrote: On Mon, Jul 25, 2011 at 11:53 AM, Mark Morgan Lloyd markmll.fpc-pas...@telemetry.co.uk wrote: Link below looks directly relevant: it contains polygon data for each timezone, subject to knowing the current location. http://efele.net/maps/tz/world/ I've confirmed that files are accessible (i.e. not subscription-only) but not investigated the format of the content. Location may be off too. All of the online location reporting tool lists my work machine as being in TX, even though I'm in TN, since all our networks are routed back to the home office before hitting the cloud. Yes, I noticed several ways of getting timezone info knowing a named location (I'm reluctant to use the terms city or town here, since they mean different things in different countries) and they obviously depend on the apparent location being relevant. The files I found were the only source of raw geographical data which could be used with e.g. a GPS receiver or manual lat/long entry (on or over land). Since they appear to contain timezones in the form Europe/London they should be compatible with e.g. Postgres's internal tables that describe named timezones, although I haven't investigated whether there's a one-to-one mapping. I assume that the Postgres files are GPL, so could be extracted for other use. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: RE : [fpc-pascal] Timezone information in a dataset datetime field?- updated
On 7/25/2011 15:53, José Mejuto wrote: Hello FPC-Pascal, Monday, July 25, 2011, 6:23:21 PM, you wrote: JH And even if dates are correctly stored in UTC it is not easy to reliably JH get back the local time. Daylight savings were changed in the past JH (and may also change in the future). If you have a date/time of JH 2001-05-01 18:00 UTC it is not easy to predict what local time it was in JH time zone x. You would need a history of all daylight saving algorithms JH of the past and the future for all time zones (or even countries). This already exists, but future settings are impossible to predict, so a local time in the future is a no-no and should be avoided as much as possible. while i agree, i feel that it is also important to point out that one may easily perform future date calculations based on several different formulas with the understanding that they may not be accurate when that date arrives if the formula changes for some reason... the recent examples given are the US daylight saving time changes (note: NOT daylight savings [specifically note there is NO 's' on daylightsaving]) but it is easy enough to work with this... i've done it for years with TP3 and TP6 code... easier in recent years but still... i've formulas from somewhere that convert between numerous calendars... i believe i even have one for the myan calendar but like most of what i have, it is all older TP3-6 code and it is also a matter if me finding it in my library :? for general purposes, storing in local time is OK but it really needs to include the timezone info for the local time or it needs to be converted to UTC so that multi-timezone apps can properly correlate the actual events on a uniform timeline... this can be a very critical point in some applications... PoS apps, being one... database transactions being another one... ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
