SQL format for converting to microseconds?

I followed this tutorial and noticed that my timestamps were wrong in the database;

"stock_symbol","current_price","high_price","low_price","open_price","percent_change","tradets","ts"
"EBAY",52.79,52.915,52.39,52.81,52.87,"1969-12-31T23:49:51.502912Z","2024-07-09T10:45:32.955649Z"

(notice the 1969-12-31 in the tradets value which should be the last traded timestamp, i.e. very recently)

That tutorial is using this code to build the query

    query = f"""
    INSERT INTO quotes(stock_symbol, current_price, high_price, low_price, open_price, percent_change, tradets, ts)
    VALUES(
        '{symbol}',
        {quote["c"]},
        {quote["h"]},
        {quote["l"]},
        {quote["o"]},
        {quote["pc"]},
        {quote["t"]} * 1000000,
        systimestamp()
    );
    """

which produces something like:

    INSERT INTO quotes(stock_symbol, current_price, high_price, low_price, open_price, percent_change, tradets, ts)
    VALUES(
        'DOCN',
        34.67,
        35.11,
        34.21,
        34.72,
        34.72,
        1720468802 * 1000000,
        systimestamp()
    );

but that is clearly not converting from epoch seconds to microseconds correctly.

I tried something like this;

SELECT
  to_utc(1720468802000000, 'Europe/Berlin'),
  to_utc(1720468802 * 1000000, 'Europe/Berlin'),
  to_utc(1720468802, 'Europe/Berlin');

and it produces

"to_utc","to_utc1","to_utc2"
"2024-07-08T18:00:02.000000Z","1969-12-31T22:49:52.502912Z","1969-12-31T23:28:40.468802Z"

which is confusing, because it’s now not clear what 1720468802 * 1000000 is actually doing. it doesn’t produce an error

1 Like

Ah I see.

Its interpreting it as an int and its getting rolled over

SELECT 1720468802 * 1000000L;

this works ok.

Hi @tolland ,

Thanks for the report. In this instance, the multiplication gets compiled to a MulIntFunctionFactory which can suffer overflow. It should really use longs, since these are timestamps.

We instead need to compile it to MulLongFunctionFactory.

Thank you for the report, I will raise a bug issue for it.

Regarding that post, its a few years old now, so we should probably review it for correctness!

EDIT: friction when converting timestamps due to numeric overflow · Issue #4752 · questdb/questdb · GitHub