[Geoserver-users] ImageMosaic timeregex + PostGres DB giving wrong time.

Jeroen_van_Gent · March 2, 2023, 3:55pm

Hello all,

I have been trying to create an Imagemosaic GeoTiff data store, using a Postgres database.

The typical filename pattern for the GeoTiffs is:

s5p_tropomi_so2vcd_epsg3857_{yyyyMMdd}T{HHmmss}_{orbitnumber}.tiff, for example:

s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff.

I have declared the following regular expression in ‘timeregex.properties’:

regex=[0-9]{8}T[0-9]{6},format=‘‘yyyyMMdd’T’HHmmss’’

I can successfully create the store, and files seems to be properly ingested into the database, but when looking in the database table, the entries seem to give the correct date, but the wrong time, at least for the hour field:

| location                                               | ingestion           |

| s5p_tropomi_so2vcd_epsg3857_20230213T120806_27653.tiff | 2023-02-13 13:08:06 |

| s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff | 2022-06-04 04:13:41 |

| s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff | 2022-06-04 23:04:15 |

Some relevant lines from the GeoServer logs:

SELECT count(*) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE ("location" = ? AND "location" IS NOT NULL )
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - 1 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" LIMIT 1
2023-03-02 16:22:56,602 DEBUG [geotools.jdbc] - SELECT ST_AsText(ST_Force2D(ST_Envelope(ST_Extent("the_geom"::geometry)))) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test"
2023-03-02 16:22:56,604 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE 0 = 1
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting the_geom to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 0 to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting location to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 1 to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting ingestion to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 2 to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Inserting new features with ps: INSERT INTO "public"."s5p_tropomi_so2vcd_epsg3857_test" ( "the_geom","location","ingestion" ) VALUES ( ST_GeomFromWKB(?, 3857),?,?)
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 1 = POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 2 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 3 = 2022-06-04 04:13:41.0

For what it’s worth: the *.properties files are in the same folder as my data files.

I am using GeoServer 2.20.2.

Any suggestions?

Thanks,

Jeroen

ianturton · March 2, 2023, 4:51pm

The times in the database will be in UTC, and I guess you’re in UTC+!. Add -Duser.timezone=GMT to make it work as you expect

Ian

···

Ian Turton

Jeroen_van_Gent · March 2, 2023, 7:22pm

Thank you, But it that case I would expect a constant delta time between the times in the file name and those in the DB, which is not the case.

But I will double check.

Jeroen

···

On 2/03/2023 17:51, Ian Turton wrote:

The times in the database will be in UTC, and I guess you’re in UTC+!. Add -Duser.timezone=GMT to make it work as you expect

Ian

On Thu, 2 Mar 2023 at 16:37, Jeroen van Gent <Jeroen.vanGent@anonymised.com> wrote:
Hello all,

I have been trying to create an Imagemosaic GeoTiff data store, using a Postgres database.

The typical filename pattern for the GeoTiffs is:

s5p_tropomi_so2vcd_epsg3857_{yyyyMMdd}T{HHmmss}_{orbitnumber}.tiff, for example:

s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff.

I have declared the following regular expression in ‘timeregex.properties’:

regex=[0-9]{8}T[0-9]{6},format=‘‘yyyyMMdd’T’HHmmss’’

I can successfully create the store, and files seems to be properly ingested into the database, but when looking in the database table, the entries seem to give the correct date, but the wrong time, at least for the hour field:
| location                                               | ingestion           |
| s5p_tropomi_so2vcd_epsg3857_20230213T120806_27653.tiff | 2023-02-13 13:08:06 |
| s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff | 2022-06-04 04:13:41 |
| s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff | 2022-06-04 23:04:15 |
Some relevant lines from the GeoServer logs:
SELECT count(*) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE ("location" = ? AND "location" IS NOT NULL )
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - 1 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" LIMIT 1
2023-03-02 16:22:56,602 DEBUG [geotools.jdbc] - SELECT ST_AsText(ST_Force2D(ST_Envelope(ST_Extent("the_geom"::geometry)))) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test"
2023-03-02 16:22:56,604 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE 0 = 1
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting the_geom to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 0 to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting location to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 1 to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting ingestion to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 2 to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Inserting new features with ps: INSERT INTO "public"."s5p_tropomi_so2vcd_epsg3857_test" ( "the_geom","location","ingestion" ) VALUES ( ST_GeomFromWKB(?, 3857),?,?)
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 1 = POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 2 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 3 = 2022-06-04 04:13:41.0
For what it’s worth: the *.properties files are in the same folder as my data files.

I am using GeoServer 2.20.2.

Any suggestions?

Thanks,

Jeroen

Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:

Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/

The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer

Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–

Ian Turton

	Virusvrij.www.avast.com

Wal_Jan_Tjalling_van · March 2, 2023, 8:16pm

Hello Jeroen,

My hunch is that Ian is correct.

The delta is one hour I February (winter time or UTC + 1)
And two hours for dates in April (summer time or UTC + 2).

Kind regards, Jan Tjalling van der Wal

···

From: Jeroen van Gent jeroen.vangent@anonymised.com
Sent: Thursday, March 2, 2023 8:22:18 PM
To: Ian Turton ijturton@anonymised.com
Cc: geoserver-users@lists.sourceforge.net geoserver-users@anonymised.comsourceforge.net
Subject: Re: [Geoserver-users] ImageMosaic timeregex + PostGres DB giving wrong time.

Thank you, But it that case I would expect a constant delta time between the times in the file name and those in the DB, which is not the case.

But I will double check.

Jeroen

On 2/03/2023 17:51, Ian Turton wrote:

The times in the database will be in UTC, and I guess you’re in UTC+!. Add -Duser.timezone=GMT to make it work as you expect

Ian

On Thu, 2 Mar 2023 at 16:37, Jeroen van Gent <Jeroen.vanGent@anonymised.com> wrote:
Hello all,

I have been trying to create an Imagemosaic GeoTiff data store, using a Postgres database.

The typical filename pattern for the GeoTiffs is:

s5p_tropomi_so2vcd_epsg3857_{yyyyMMdd}T{HHmmss}_{orbitnumber}.tiff, for example:

s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff.

I have declared the following regular expression in ‘timeregex.properties’:

regex=[0-9]{8}T[0-9]{6},format=‘‘yyyyMMdd’T’HHmmss’’

I can successfully create the store, and files seems to be properly ingested into the database, but when looking in the database table, the entries seem to give the correct date, but the wrong time, at least for the hour field:
| location                                               | ingestion           |
| s5p_tropomi_so2vcd_epsg3857_20230213T120806_27653.tiff | 2023-02-13 13:08:06 |
| s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff | 2022-06-04 04:13:41 |
| s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff | 2022-06-04 23:04:15 |
Some relevant lines from the GeoServer logs:
SELECT count(*) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE ("location" = ? AND "location" IS NOT NULL )
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - 1 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" LIMIT 1
2023-03-02 16:22:56,602 DEBUG [geotools.jdbc] - SELECT ST_AsText(ST_Force2D(ST_Envelope(ST_Extent("the_geom"::geometry)))) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test"
2023-03-02 16:22:56,604 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE 0 = 1
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting the_geom to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 0 to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting location to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 1 to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting ingestion to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 2 to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Inserting new features with ps: INSERT INTO "public"."s5p_tropomi_so2vcd_epsg3857_test" ( "the_geom","location","ingestion" ) VALUES ( ST_GeomFromWKB(?, 3857),?,?)
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 1 = POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 2 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 3 = 2022-06-04 04:13:41.0
For what it’s worth: the *.properties files are in the same folder as my data files.

I am using GeoServer 2.20.2.

Any suggestions?

Thanks,

Jeroen

Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:

Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/

The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer

Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–

Ian Turton

	Virusvrij.www.avast.com

Jeroen_van_Gent · March 3, 2023, 8:29am

Okay, I see. Thank you very much for pointing out this behaviour.

Jeroen

···

On 2/03/2023 21:16, Wal, Jan Tjalling van der wrote:

Hello Jeroen,

My hunch is that Ian is correct.

The delta is one hour I February (winter time or UTC + 1)
And two hours for dates in April (summer time or UTC + 2).

Kind regards, Jan Tjalling van der Wal

Sent from Outlook for Android

From: Jeroen van Gent jeroen.vangent@anonymised.com
Sent: Thursday, March 2, 2023 8:22:18 PM
To: Ian Turton ijturton@anonymised.com
Cc: geoserver-users@lists.sourceforge.net geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] ImageMosaic timeregex + PostGres DB giving wrong time.

Thank you, But it that case I would expect a constant delta time between the times in the file name and those in the DB, which is not the case.

But I will double check.

Jeroen

On 2/03/2023 17:51, Ian Turton wrote:
The times in the database will be in UTC, and I guess you’re in UTC+!. Add -Duser.timezone=GMT to make it work as you expect

Ian

On Thu, 2 Mar 2023 at 16:37, Jeroen van Gent <Jeroen.vanGent@anonymised.com> wrote:
Hello all,

I have been trying to create an Imagemosaic GeoTiff data store, using a Postgres database.

The typical filename pattern for the GeoTiffs is:

s5p_tropomi_so2vcd_epsg3857_{yyyyMMdd}T{HHmmss}_{orbitnumber}.tiff, for example:

s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff.

I have declared the following regular expression in ‘timeregex.properties’:

regex=[0-9]{8}T[0-9]{6},format=‘‘yyyyMMdd’T’HHmmss’’

I can successfully create the store, and files seems to be properly ingested into the database, but when looking in the database table, the entries seem to give the correct date, but the wrong time, at least for the hour field:
| location                                               | ingestion           |
| s5p_tropomi_so2vcd_epsg3857_20230213T120806_27653.tiff | 2023-02-13 13:08:06 |
| s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff | 2022-06-04 04:13:41 |
| s5p_tropomi_so2vcd_epsg3857_20220604T210415_27650.tiff | 2022-06-04 23:04:15 |
Some relevant lines from the GeoServer logs:
SELECT count(*) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE ("location" = ? AND "location" IS NOT NULL )
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - 1 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,601 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" LIMIT 1
2023-03-02 16:22:56,602 DEBUG [geotools.jdbc] - SELECT ST_AsText(ST_Force2D(ST_Envelope(ST_Extent("the_geom"::geometry)))) FROM "public"."s5p_tropomi_so2vcd_epsg3857_test"
2023-03-02 16:22:56,604 DEBUG [geotools.jdbc] - SELECT "fid",ST_AsEWKB("the_geom") as "the_geom","location","ingestion" FROM "public"."s5p_tropomi_so2vcd_epsg3857_test" WHERE 0 = 1
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting the_geom to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 0 to POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting location to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 1 to s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting ingestion to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Setting 2 to 2022-06-04 04:13:41.0
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - Inserting new features with ps: INSERT INTO "public"."s5p_tropomi_so2vcd_epsg3857_test" ( "the_geom","location","ingestion" ) VALUES ( ST_GeomFromWKB(?, 3857),?,?)
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 1 = POLYGON ((-20026376.39 -20048673.9, -20026376.39 20048966.1, 20026863.61 20048966.1, 20026863.61 -20048673.9, -20026376.39 -20048673.9))
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 2 = s5p_tropomi_so2vcd_epsg3857_20220604T021341_27650.tiff
2023-03-02 16:22:56,605 DEBUG [geotools.jdbc] - 3 = 2022-06-04 04:13:41.0
For what it’s worth: the *.properties files are in the same folder as my data files.

I am using GeoServer 2.20.2.

Any suggestions?

Thanks,

Jeroen

Geoserver-users mailing list

Please make sure you read the following two resources before posting to this list:

Earning your support instead of buying it, but Ian Turton: http://www.ianturton.com/talks/foss4g.html#/

The GeoServer user list posting guidelines: http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer

Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–

Ian Turton
Virusvrij.www.avast.com