SQL format for converting to microseconds?

Community
1

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
2

Ah I see.

2024-07-09_11-54
2024-07-09_11-54692×234 9.99 KB

Its interpreting it as an int and its getting rolled over

SELECT 1720468802 * 1000000L;

this works ok.

3

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

4

This has now been resolved and will be released soon. We aim that constant folds such as this one will not implicitly overflow, but instead be treated the same as if they were the raw, already folded value. In this case, that would mean it will be automatically casted to a LONG.