Title
#users-public
w

WCKD

11/09/2022, 3:29 PM
Hello, is there a way we can retrieve a questdb query with the timestamp in unix format instead of the default iso format?
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:10 PM
Hi
4:10 PM
Do you mean epoch timestamp, i.e. an integer?
w

WCKD

11/09/2022, 4:10 PM
Yep
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:11 PM
have you tried
cast(<your unix ts> as timestamp)
?
w

WCKD

11/09/2022, 4:12 PM
Where should I exactly try this? In the console to convert the entire row as epoch timestamps?
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:14 PM
In your SQL queries
4:14 PM
Or you're trying to fetch columns in a given format?
w

WCKD

11/09/2022, 4:14 PM
Currently my query looks like "select * from table where ts >= x and ts <= y"
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:15 PM
then try
select cast(ts as long) from my_tab
w

WCKD

11/09/2022, 4:15 PM
Thank you, will try
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:16 PM
under the hood timestamps and dates are nothing more than epoch timestamps with microsecond precision
w

WCKD

11/09/2022, 4:16 PM
Yep, I read that, but i didn't know how to make them look like epochs instead of iso format, haha
4:21 PM
It worked, @Andrey Pechkurov, thank you so much man, huge props to you!!
4:21 PM
🙏
4:34 PM
Is there any way we can get rid of the double quotes (") surrounding the returned csv fields? For example, an integer value comes as "1" instead of 1 in the csv after the query
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:49 PM
That's weird. I just tried to export a few table rows and all long/double values don't have quotes
w

WCKD

11/09/2022, 4:50 PM
Hmm, let me double check this
4:56 PM
In python for example if I do
requests.get(".../exp?query=...")
the csv retrieved as a response has the string values surrounded by double quotes (""). Sorry, it was not the doubles/ints/floats, but the strings
4:56 PM
Is there a way to retrieve the strings without double qoutes (BTCUSDT instead of "BTCUSDT")?
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:57 PM
strings may contain spaces, commas and so on, so we escape them
4:58 PM
though there is no formal RFC for CSV, all implementations I'm aware of do the same
w

WCKD

11/09/2022, 4:58 PM
Yup, that makes sense, thank you so much!
Andrey Pechkurov

Andrey Pechkurov

11/09/2022, 4:59 PM
I don't think we have a configuration to change that. If you really need this feature, please create a GH issue with a brief explanation of your use case