View Issue Details

IDProjectCategoryView StatusLast Update
0004116SymmetricDSBugpublic2022-10-04 15:15
Reporterhanes Assigned Toemiller  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.11.0 
Target Version3.14.2Fixed in Version3.14.2 
Summary0004116: Syncing from Time to Timestamp (or vice versa) in Postgres produced inaccurate values
DescriptionFor Postgres, when syncing from a Time column to a Timestamp column (or vice versa), the time amount is not the correct value. Perhaps it's an incorrect interpretation of the microseconds when doing the conversion? Time to Time, and Timestamp to Timestamp seem to work okay.
Tagsdialect: postgresql

Activities

hanes

2019-10-18 14:38

developer   ~0001617

Note that the CSV being sent seems to be correct. For example:
insert,"foo6","2019-10-18 10:29:32.307966"
for the timestamp on the source case, and
insert,"foo5","10:28:03.074378"
for the time on the source case.

FYI, for the Time to Timestamp case, the date portion of the timestamp defaults to 1/1/1970, which seems reasonable (since the time is unknown)

hanes

2019-10-18 14:39

developer   ~0001618

Issues appears to occur with initial load, as well.

hanes

2019-10-18 15:03

developer   ~0001619

This scenario with an H2 source and target works just fine. In fact, H2 as a source and Postgres as a target works as well. However, Postgres as a source and H2 as a target doesn't work. My guess is it has something to do with the six digit "microsecond" value postgres seems to use.

rudiejd

2021-08-19 19:12

developer   ~0002001

Last edited: 2021-08-19 19:14

View 2 revisions

I can confirm that I reproduced this issue on 3.12 and Postgres 13.1, but only with a Postgres source with TIMESTAMP column and an H2 target with TIME column. When I inserted into the TIMESTAMP column on the Postgres side, I got a time in the future on the H2 side. The TIMESTAMP looks fine on incoming batches, but the insert is wrong.

Related Changesets

SymmetricDS: 3.14 65388906

2022-09-06 17:47:59

evan-miller-jumpmind

Details Diff
0004116: Fixed Postgres timestamp to time and time to timestamp conversion Affected Issues
0004116
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java Diff File
mod - symmetric-db/src/test/java/org/jumpmind/db/platform/AbstractDatabasePlatformTest.java Diff File

Issue History

Date Modified Username Field Change
2019-10-18 14:36 hanes New Issue
2019-10-18 14:36 hanes Tag Attached: dialect: postgresql
2019-10-18 14:36 hanes Status new => confirmed
2019-10-18 14:38 hanes Note Added: 0001617
2019-10-18 14:39 hanes Note Added: 0001618
2019-10-18 15:03 hanes Note Added: 0001619
2019-11-14 19:49 elong Target Version 3.11.0 => 3.12.0
2020-05-26 12:27 elong Target Version 3.12.0 => 3.13.0
2021-08-19 19:12 rudiejd Note Added: 0002001
2021-08-19 19:14 rudiejd Note Edited: 0002001 View Revisions
2021-08-20 22:22 rudiejd Assigned To => rudiejd
2021-08-20 22:22 rudiejd Status confirmed => assigned
2021-11-02 20:52 elong Target Version 3.13.0 => 3.14.0
2022-07-25 16:13 elong Assigned To rudiejd =>
2022-07-25 16:13 elong Status assigned => confirmed
2022-07-25 16:13 elong Target Version 3.14.0 =>
2022-09-06 17:48 emiller Assigned To => emiller
2022-09-06 17:48 emiller Status confirmed => resolved
2022-09-06 17:48 emiller Resolution open => fixed
2022-09-06 17:48 emiller Fixed in Version => 3.14.2
2022-09-06 18:00 Changeset attached => SymmetricDS 3.14 65388906
2022-10-04 15:15 admin Status resolved => closed