datafold / data-diff

Compare tables within or across databases
https://docs.datafold.com
MIT License
2.93k stars 262 forks source link

[Postgresql] pg time type error #888

Closed Schumpeterx closed 4 months ago

Schumpeterx commented 4 months ago

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

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](https://www.postgresql.org/docs/12/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE)

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

**Describe the environment**
data-diff last version:  https://github.com/datafold/data-diff/commit/d2161ccf6705d4e6cde6069a7e5d945a7c0f1a89
pg version: PostgreSQL 12.12
glebmezh commented 4 months ago

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 support@datafold.com if you have any questions.

-Gleb