marmot transforms datetime fields

[Blackmoomba]

I use marmot alongside a python application to replicate my data to secondary/tertiary servers.

Within my database, I've got several sqlite datetime fields, such as: received DATETIME NOT NULL. My application stores dates in this field using ISO 8601 format: 2023‐08‐15T17:01:43+00:00. When marmot copies this field and sends it to a replica, the field is delivered as a unix epoch timestamp: 1692122503.

This wouldn't be an issue as python datetime can correctly parse this into the correct datetime, except that my sqlite queries which implement conditionals on this field do not work against epoch timestamps.

For example, this query does not return any results against fields who's timestamps have been transformed:

query = "SELECT * FROM table WHERE received BETWEEN  :from_date AND :to_date" 
params= {"from_date":  "2000-01-01 12:00:00+00:00", "to_date": "2030-12-31 23:59:59+00:00"}

I believe marmot should never transform customer data, and this is likely an oversight/assumption.

I'm not a Go dev, but it looks like this might be happening here in the sqlite3 driver you're using: https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L2231-L2258. I understand that if the driver behaves this way, its not immediately in your control. But I'm raising this issue to you first since you probably don't want to damage customer data anymore than I do. I would both consider alternative drivers and also add additional unit tests which test that data is never mutated.

[maxpert]

Thanks for reporting this, and I believe you might be right this might be combination of driver, and what gets serialized back. While I dig deeper to reproduce it, what was surprising to me is if the date time is being stored as a string. Can you kindly tell me what Python library + ORM are you using. As far as data loss, I won't label it as you have lost your data because these timestamps are sitting as UNIX timestamps. Nevertheless it's a quick Google told me:

SQLite does not have an official datetime type. Instead, it stores dates and times as TEXT, REAL or INTEGER values using Date and Time Functions. TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS. SSS”). https://tableplus.com/blog/2018/07/sqlite-how-to-use-datetime-value.html#:~:text=SQLite%20does%20not%20have%20an,SSS%E2%80%9D).

Now question for me is to see if I can detect if it's string or integer or real. With how driver is converting it under the hood, it might be challenging.

[Blackmoomba]

You're correct that the data is not lost - just unexpectedly transformed, which caused quite a bit of troubleshooting on my end.

With that said, I'm using python's standard sqlite3 library. I'm inserting into the sql datetime fields via the datetime class's __str__() method, which returns an ISO8601 formatted string.

IE:

In [1]: from datetime import datetime, timezone

In [2]: dt = datetime.now(tz=timezone.utc)

In [3]: dt
Out[3]: datetime.datetime(2023, 8, 16, 20, 23, 18, 105371, tzinfo=datetime.timezone.utc)

In [4]: str(dt)
Out[4]: '2023-08-16 20:23:18.105371+00:00'   # <-- this is what I'm inserting to sqlite3 db as a param

[maxpert]

Just to update, I am able to reproduce the issue. The code in Marmot is pretty straight forward and I am trying to see if I can monkey patch something to unblock you. So far dead-ends I will post once I land something.

[maxpert]

Update 2: I've Identified the issue, will be working over it to get the fix out. Next version should have the fix in it.

[maxpert]

@Blackmoomba I will release a beta version in a while, I've verified on my end that it works. Can you help me test that it works for you as well?

Here is before (first insert and select) and after (second insert after fix) that I am seeing:

[Blackmoomba]

Thanks. I'll test the change tomorrow and let you know if everything appears to be working.

[Blackmoomba]

This fixed the issue. Thanks very much for resolving this so quickly.

[maxpert]

Prefect I will wait for a few days for you to double check and close the ticket.