[DuckDB][UnixTimeMs & UnixTimeS] - Time functions are returning local timestamp, not UTC

[idiom-bytes]

Describe the bug

Hi @calina-c, I have tested the issue further and the current UTC flags & implementations are not returning an UTC timestamp, but rather, a local timestamp. This is leading to a variety of data problems.

Problem (1) many of our UnixTImeS + UnixTImeMs functions are returning local timestamp results.

TLDR; when asked GPT about it... "The replace method in Python's datetime module is used to replace specified date attributes from a datetime object. However, it doesn't perform any timezone conversions. It simply replaces the tzinfo attribute with the specified timezone, in this case timezone.utc."

I wrote a simple test to see if the unix timestamp was close to the UTC values that should have been yielded.. It verifies that how we use "natural language" to get "now" isn't the same as UnixTimeS.now()

Problem (2) as you can see, using "now" will return a local timestamp now, rather than a unix (or GMT/UTC) now.

Problem (3) is that @kdetry or perhaps others, are using it wrongly I believe in parts of the code.

# from app.py line 237
ms_end_ts_from_utc = UnixTimeMs(int(datetime.utcnow().timestamp())) #in seconds
s_end_ts_from_utc = UnixTimeS(int(datetime.utcnow().timestamp())) #in seconds

It also checks other implementations (like what's going on in app.py, and points out that it's wrong) @kdetry.

We can see that all of the unix tmestamp now() commands worked... except for the first, natural language one. The first one, is giving us local timestamp. (UTC-7)

- ms_end_ts_from_timestr_now 1716213309546
- ms_end_ts_from_utc 1716263709
- s_end_ts_from_utc 1716263709
- time.time() 1716238509
- UnixTimeMs.now() 1716213309546
- UnixTimeS.now() 1716213309

We're trying to sync to "now" but subscriptions subgraph is fetching from 7 hours in the past...

What I want, is lake_ss.end_ts("now") to return time.time(), such that we can fetch the right data from subgraph and beyond.

DoD:

• UnixTimeS and UnixTimeMs shouldn't operate off local timestampimplement
• "now" should return UTC time "now", not local time "now"

[idiom-bytes]

I had to remove some logic from formatting timestamps to UTC in order to get the proper unixtimestamp, and be able to sync-to-latest. There are still some issues going on, but I was able to at least move the fetch/dates closer to the desired outcome...

These are the two problems that exist here:

1. Fetching is using the current unix timestamp
2. Fetching pd_subscriptions is only 3 hours away from head (rather than 7)

Original Problem

This is what i got originally... (7hrs to GMT, and the 7hr difference on subscriptions... still TBD)

Target Solution

This is closer to what we want... (which is current unix timestamp, or gmt/utc)... This is on-time w/ GMT, and there is a 3hr difference on subscriptions... still TBD (matches up w/ what @KatunaNorbert had managed to fix as well)

[idiom-bytes]

When it goes to calculate timestr from natural language, and gets "now", (1) this now will be based on local timestamp.

In my case (7 hour difference) will be used to fetch from subgraph as the end_ts.

It seems that rather than getting UTC time, the time retrieved is rather UTC-7 (my local time zone)... being 7 hours earlier than it should.

[idiom-bytes]

This has been completed on PR #1071