[Postgresql] pg time type error

[Schumpeterx]

Describe the bug
I found three errors when using data-diff with Postgresql time type.

• database prepare
  for pg and mysql

CREATE TABLE test_time2 (id int ,time_col time(3));
insert into test_time2 values(1,'08:59:59.000');

1

15:59:49 ERROR    function round(double precision, integer) does not exist                                                                         __main__.py:344
                  LINE 1: ...00:00' as time) + make_interval(0, 0, 0, 0, 0, 0, ROUND(EXTR...                                                                      
                                                                               ^                                                                                  
                  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.   

when running the blow sql query in pg, it said that ERROR: function round(double precision, integer) does not exist (SQLSTATE 42883)

# data-diff output sql
 SELECT "id"::varchar, to_char(CAST('00:00:00' as time) + make_interval(0, 0, 0, 0, 0, 0, ROUND(EXTRACT( epoch from "time_col"),                 
                  3)), 'hh24:mi:ss.ff6') FROM "test_time2" WHERE ("id" >= 1) AND ("id" < 2)

That is because the type of EXTRACT( epoch from "time_col") is double precision.

• quick fix: cast EXTRACT( epoch from "time_col") to numeric. CAST(EXTRACT( epoch from "time_col") as NUMERIC)

2

After fix the first problem, i got another one: the return time is incorrect. Time 08:59:59.000 will returned as '08:59:59.ff6

• quick fix: change hh24:mi:ss.ff6 to hh24:mi:ss.US link

3

Besides, data-diff dose not support mysql time type now.

Describe the environment
data-diff last version: d2161cc
pg version: PostgreSQL 12.12

[glebmezh]

Hi @Schumpeterx,

Thank you for trying out data-diff and for taking the time to open this issue. We made a hard decision to sunset the data-diff package and won't provide further development or support. Diffing functionality will continue to be available in Datafold Cloud. We have completely rewritten the diffing engine in the cloud over the past few months and have solved the fundamental issues with the original algorithm used in the data-diff package. Feel free to take it for a trial or contact us at [email protected] if you have any questions.

-Gleb