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.

Sign up for GitHub

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

Jump to bottom

Inconsistent behavior of timezone (nTZFlag) of DateTime fields among drivers #2696

Closed
rbuffat opened this issue Jun 17, 2020 · 5 comments
Closed

Inconsistent behavior of timezone (nTZFlag) of DateTime fields among drivers #2696

rbuffat opened this issue Jun 17, 2020 · 5 comments

Comments

@rbuffat
Copy link
Contributor

rbuffat commented Jun 17, 2020

I noticed that different drivers handle the nTZFlag of OGR_F_SetFieldDateTimeEx differently. I do not know the correct behavior of the drivers should be, thus everything could be as it should be expected!

The documentation of OGR_F_SetFieldDateTimeEx mentions 1=localtime, however, it seems as this is either converted to 0=unknown or 100=GMT. The documentation mentions also "see data model for details", but I'm unsure where to find this information (https://gdal.org/user/vector_data_model.html seems not to contain information regarding timezones).

The GPKG driver seems only to support GMT timezone and either convert to GMT for 0=unknown or fails.
The GPKG Specification mentions the following for DATETIME:

ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM:SS.SSSZ with T separator character > and Z suffix for coordinated universal time (UTC) encoded in either UTF-8 or UTF-16. See TEXT. > Stored as SQLite TEXT.
http://www.geopackage.org/spec/ => Table 1. GeoPackage Data Types

Mapinfo seems not to support timezone at all.

from osgeo import gdal
from osgeo import ogr

print("GDAL:", gdal.__version__)

gdal.UseExceptions()


def error_handler(err_level, err_no, err_msg):
    print(err_level, err_no, err_msg)


gdal.PushErrorHandler(error_handler)

for driver, filename in [('GeoJSON', '/vsimem/test.geojson'),
                         ('GPKG', '/vsimem/test.gpkg'),
                         ('MapInfo File', '/vsimem/test.tab'),
                         # ('CSV', '/vsimem/test.csv'),  # converts field to string
                         ('GeoJSONSeq', '/vsimem/test.geojsons'),
                         # ('GML', '/vsimem/test.gml'),  # converts field to string
                         # ('GPX', '/vsimem/test.gpx'),
                         # ('GPSTrackMaker', '/vsimem/test.gtm'),
                         # ('PCIDSK', '/vsimem/test.pix') # converts field to string
                         ]:

    print(driver)
    ds = ogr.GetDriverByName(driver).CreateDataSource(filename)
    lyr = ds.CreateLayer("timezone")
    lyr.CreateField(ogr.FieldDefn("unknown_tz", ogr.OFTDateTime))
    lyr.CreateField(ogr.FieldDefn("local_tz", ogr.OFTDateTime))
    lyr.CreateField(ogr.FieldDefn("gmt_tz", ogr.OFTDateTime))
    lyr.CreateField(ogr.FieldDefn("met_tz", ogr.OFTDateTime))
    f = ogr.Feature(lyr.GetLayerDefn())

    # OGR_F_SetFieldDateTimeEx: (0=unknown, 1=localtime, 100=GMT, see data model for details)
    # OGR_F_GetFieldAsDateTimeEx: (0=unknown, 1=localtime, 100=GMT, see data model for details)
    # CPLParseRFC822DateTime: (0=unknown, 100=GMT, 101=GMT+15minute, 99=GMT-15minute), or NULL
    f.SetField("unknown_tz", 2020, 1, 2, 12, 30, 1, 0)
    f.SetField("local_tz", 2020, 1, 2, 12, 30, 1, 1)
    f.SetField("gmt_tz", 2020, 1, 2, 12, 30, 1, 100)
    f.SetField("met_tz", 2020, 1, 2, 12, 30, 1, 96)
    lyr.CreateFeature(f)
    ds = None

    ds = ogr.Open(filename)
    lyr = ds.GetLayer(0)
    layerDefinition = lyr.GetLayerDefn()
    for i in range(layerDefinition.GetFieldCount()):
        fieldTypeCode = layerDefinition.GetFieldDefn(i).GetType()
        print(layerDefinition.GetFieldDefn(i).GetName(), layerDefinition.GetFieldDefn(i).GetFieldTypeName(fieldTypeCode))
    f = lyr.GetNextFeature()

    for field in ["unknown_tz", "local_tz", "gmt_tz", "met_tz"]:
        print("Field: {} \tGetFieldAsDateTime: '{}' "
              "\tGetFieldAsString: '{}'".format(field,
                                                str(f.GetFieldAsDateTime(field)),
                                                f.GetFieldAsString(field)))
    ds = None
    ogr.GetDriverByName(driver).DeleteDataSource(filename)
    print("")

Gives the following output:

GDAL: 3.0.4
GeoJSON
unknown_tz DateTime
local_tz DateTime
gmt_tz DateTime
met_tz DateTime
Field: unknown_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: local_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: gmt_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: met_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 96]' 	GetFieldAsString: '2020/01/02 12:30:01-01'

GPKG
unknown_tz DateTime
local_tz DateTime
gmt_tz DateTime
met_tz DateTime
Field: unknown_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: local_tz 	GetFieldAsDateTime: '[0, 0, 32, 0, 1961831200, 4.5591245536807923e-41, 1334297120]' 	GetFieldAsString: ''
Field: gmt_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: met_tz 	GetFieldAsDateTime: '[0, 0, 32, 0, 1961831200, 4.5591245536807923e-41, 1334297120]' 	GetFieldAsString: ''

MapInfo File
unknown_tz DateTime
local_tz DateTime
gmt_tz DateTime
met_tz DateTime
Field: unknown_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: local_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: gmt_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: met_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'

GeoJSONSeq
2 1 No SRS set on layer. Assuming it is long/lat on WGS84 ellipsoid
unknown_tz DateTime
local_tz DateTime
gmt_tz DateTime
met_tz DateTime
Field: unknown_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: local_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: gmt_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: met_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 96]' 	GetFieldAsString: '2020/01/02 12:30:01-01'
@rouault
Copy link
Member

rouault commented Jun 17, 2020

mentions 1=localtime, however, it seems as this is either converted to 0=unknown or 100=GMT

Yes, I doubt any driver (except the Memory one) is able to encode the concept of 'localtime'.

The documentation mentions also "see data model for details", but I'm unsure where to find this information (https://gdal.org/user/vector_data_model.html seems not to contain information regarding timezones).

Probably it was planned to be written, but I'm not aware of any existing further documentation regarding timezones.

For GPKG, there has been a fix in 3.1. You'd now get

GPKG
('unknown_tz', 'DateTime')
('local_tz', 'DateTime')
('gmt_tz', 'DateTime')
('met_tz', 'DateTime')
(2, 1, 'Non-conformant content for record 1 in column local_tz, 2020/01/02 12:30:01, successfully parsed')
Field: unknown_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: local_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]' 	GetFieldAsString: '2020/01/02 12:30:01'
Field: gmt_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 100]' 	GetFieldAsString: '2020/01/02 12:30:01+00'
Field: met_tz 	GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 96]' 	GetFieldAsString: '2020/01/02 12:30:01-01'

Mapinfo seems not to support timezone at all.

Yes, it doesn't. It currently completely ignores the timezone info at all. Should it convert into GMT when there's a timezone ? That'd be arguable... I'm not sure which semantics MapInfo itself attaches to datetimes.

@rbuffat rbuffat mentioned this issue Jun 19, 2020
Open
@rbuffat
Copy link
Contributor Author

rbuffat commented Jun 20, 2020

there's a timezone ? That'd be arguable... I'm not sure which semantics MapInfo itself attaches to datetimes.

I'm not familiar enough with the MapInfo file format. As far as I can see it is not possible to find out if a driver supports timezones or not, except by testing it?

I found another strange result for GPSTrackMaker:

from osgeo import gdal
from osgeo import ogr
import osgeo.osr as osr

print("GDAL:", gdal.__version__)

gdal.UseExceptions()


def error_handler(err_level, err_no, err_msg):
    print(err_level, err_no, err_msg)


gdal.PushErrorHandler(error_handler)

for driver, filename in [
                         # ('GeoJSON', '/vsimem/test.geojson'),
                         # ('GPKG', '/vsimem/test.gpkg'),
                         # ('MapInfo File', '/vsimem/test.tab'),
                         # ('CSV', '/vsimem/test.csv'),  # converts field to string
                         # ('GeoJSONSeq', '/vsimem/test.geojsons'),
                         # ('GML', '/vsimem/test.gml'),  # converts field to string
                         # ('GPX', '/vsimem/test.gpx'),
                         ('GPSTrackMaker', '/vsimem/test.gtm'),
                         # ('PCIDSK', '/vsimem/test.pix') # converts field to string
                         ]:

    srs = osr.SpatialReference()
    srs.ImportFromEPSG(4326)

    print(driver)
    ds = ogr.GetDriverByName(driver).CreateDataSource(filename)
    lyr = ds.CreateLayer("test", srs, ogr.wkbPoint)
    lyr.CreateField(ogr.FieldDefn("name", ogr.OFTString))
    lyr.CreateField(ogr.FieldDefn("comment", ogr.OFTString))
    lyr.CreateField(ogr.FieldDefn("icon", ogr.OFTInteger))
    lyr.CreateField(ogr.FieldDefn("time", ogr.OFTDateTime))
    f = ogr.Feature(lyr.GetLayerDefn())

    # OGR_F_SetFieldDateTimeEx: (0=unknown, 1=localtime, 100=GMT, see data model for details)
    # OGR_F_GetFieldAsDateTimeEx: (0=unknown, 1=localtime, 100=GMT, see data model for details)
    # CPLParseRFC822DateTime: (0=unknown, 100=GMT, 101=GMT+15minute, 99=GMT-15minute), or NULL

    point = ogr.Geometry(ogr.wkbPoint)
    point.AddPoint(1.0, 1.0)
    f.SetGeometry(point)
    f.SetField("name", "name")
    f.SetField("comment", "test")
    f.SetField("icon", 1)
    f.SetField("time", 2020, 1, 2, 12, 30, 1, 0)
    lyr.CreateFeature(f)

    point = ogr.Geometry(ogr.wkbPoint)
    point.AddPoint(1.0, 1.0)
    f.SetGeometry(point)
    f.SetField("name", "name")
    f.SetField("comment", "test")
    f.SetField("icon", 1)
    f.SetField("time", 2020, 1, 2, 12, 30, 1, 1)
    lyr.CreateFeature(f)

    point = ogr.Geometry(ogr.wkbPoint)
    point.AddPoint(1.0, 1.0)
    f.SetGeometry(point)
    f.SetField("name", "name")
    f.SetField("comment", "test")
    f.SetField("icon", 1)
    f.SetField("time", 2020, 1, 2, 12, 30, 1, 100)
    lyr.CreateFeature(f)

    point = ogr.Geometry(ogr.wkbPoint)
    point.AddPoint(1.0, 1.0)
    f.SetGeometry(point)
    f.SetField("name", "name")
    f.SetField("comment", "test")
    f.SetField("icon", 1)
    f.SetField("time", 2020, 1, 2, 12, 30, 1, 80)
    lyr.CreateFeature(f)

    point = ogr.Geometry(ogr.wkbPoint)
    point.AddPoint(1.0, 1.0)
    f.SetGeometry(point)
    f.SetField("name", "name")
    f.SetField("comment", "test")
    f.SetField("icon", 1)
    f.SetField("time", 2020, 1, 2, 12, 30, 1, 130)
    lyr.CreateFeature(f)

    ds = None

    ds = ogr.Open(filename)
    for f in lyr:
        for field in ["time"]:
            print("Field: {} \tGetFieldAsDateTime: '{}' "
                  "\tGetFieldAsString: '{}'".format(field,
                                                    str(f.GetFieldAsDateTime(field)),
                                                    f.GetFieldAsString(field)))
    ds = None
    ogr.GetDriverByName(driver).DeleteDataSource(filename)
    print("")

Output:

GDAL: 3.2.0dev-104651c3a1
GPSTrackMaker
Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]'      GetFieldAsString: '2020/01/02 12:30:01'
Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 54, 46.0, 0]'     GetFieldAsString: '2020/01/02 12:54:46'
Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 30, 1.0, 0]'      GetFieldAsString: '2020/01/02 12:30:01'
Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 35, 1.0, 0]'      GetFieldAsString: '2020/01/02 12:35:01'
Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 22, 31.0, 0]'     GetFieldAsString: '2020/01/02 12:22:31'

rouault added a commit that referenced this issue Jun 20, 2020
@rouault
GTM: on write, do not consider TZFlag=1 (localtime) as a timezone val…
11fc52a 
…ue (refs #2696)
rouault added a commit that referenced this issue Jun 20, 2020
@rouault
GTM: on write, do not consider TZFlag=1 (localtime) as a timezone val…
e3a2bc5 
…ue (refs #2696)
@rouault
Copy link
Member

rouault commented Jun 20, 2020

As far as I can see it is not possible to find out if a driver supports timezones or not, except by testing it?

No there's no metadata for that currently

I found another strange result for GPSTrackMaker:

OK, looking at the driver, it normalizes datetime to UTC taking into account the timezone information. The only real issue was with it dealing with TZFlag=1 (localtime) as it was a normal time zone. I've just fixed that (to ignore it, ie consider it as unknown/UTC)

@rbuffat
Copy link
Contributor Author

rbuffat commented Jun 20, 2020 •
edited
Loading

Is the UTC conversion done correctly?

>>> datetime.datetime(2020, 1, 2, 12, 30, 1) + datetime.timedelta(minutes=(130-100) * 15)
datetime.datetime(2020, 1, 2, 20, 0, 1)

while f.SetField("time", 2020, 1, 2, 12, 30, 1, 130)
returns

Field: time     GetFieldAsDateTime: '[2020, 1, 2, 12, 22, 31.0, 0]'     GetFieldAsString: '2020/01/02 12:22:31'

gdal/gdal/ogr/ogrsf_frmts/gtm/gtmwaypointlayer.cpp

Line 158 in e3a2bc5

unixTime -= (TZFlag - 100) * 15;

Isn't unixTime in seconds and (TZFlag - 100) * 15 in minutes?

Sorry, I should have been more clear in what I meant.

rouault added a commit that referenced this issue Jun 20, 2020
@rouault
GTM: on write, take into correctly timezone value to convert to UTC (…
6f6da6f 
…refs #2696)
@rouault
Copy link
Member

rouault commented Jun 20, 2020

Isn't unixTime in seconds and (TZFlag - 100) * 15 in minutes?

yes, that's that. In your above snippet, the timedela should be subtracted, not added (I believe...)

rouault added a commit that referenced this issue Jun 20, 2020
@rouault
GTM: on write, take into correctly timezone value to convert to UTC (…
20cc789 
…refs #2696)
@rbuffat rbuffat closed this as completed Oct 7, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@rbuffat @rouault