Extract time from datetime field in SQL Runner

[kuranyi]

Like the title says, I’m trying to extract the time field from a datetime field. Datetime is standard format of YYYY-MM-DD HH:MM:SS. It would appear the CONVERT or CAST to TIME function does not work in SQL Runner so unsure if there is another solution to this or not? 

Thank you for your help!

[Dawid]

That’s interesting. I’ve tested TIME(<datetime_field>) in three different databases:

• MySQL
• BigQuery
• Snowflake

And in all of them the result field was indeed a full datetime not just the time part. Must be a bug

[jbendinger-1633]

I think the SQL runner just runs the query against your database, so you should just check the documentation for the functions supported by your dialect. I believe that should fix your problem.

[Dawid]

Actually I don't think so because the TIME() function should only return the time part as hh:mm:SS but it still shows up as a datetime, so there could either be some kind of transfirmztion going on or, time zone conversion that outputs it as datetime/timestamp 

[kuranyi]

I think the SQL runner just runs the query against your database, so you should just check the documentation for the functions supported by your dialect. I believe that should fix your problem.

Any idea where I would locate that documentation? 

[Dawid]

I haven’t found anything in-depth about SQL Runner’s mechanics. I wouldn’t be surprised if this were a bug of some sort

[kuranyi]

I haven’t found anything in-depth about SQL Runner’s mechanics. I wouldn’t be surprised if this were a bug of some sort

Super sparse to find any formal documental on SQL Runner. EXTRACT seems to work correctly, so in theory I could probably extract the time in join as a string, no?

[Dawid]

At first I thought it was a bug in the client-side layer.

Now I thing it’s much earlier. I looked at raw data results from the query AJAX call:

This was using TIME(created_at) which should clearly come as 11:37:00, 08:17:09, and so on. 

Interestingly the type of the column was detected as string and datatype as NULL. 

Wouldn’t be surprised if, before sending the results, it goes through some kind of TO_STRING function with default settings that produce these undesired results.

[jbendinger-1633]

I think the SQL runner just runs the query against your database, so you should just check the documentation for the functions supported by your dialect. I believe that should fix your problem.

Any idea where I would locate that documentation? 

It depends on the SQL dialect you’re using. As an example, I use BigQuery standard SQL and all the documentation is here. As another example, PostgreSQL is another SQL dialect and its documentation is found here. The documentation spaces have sections where the different available functions are documented. You should look for the function you’re trying to use (probably within a section called “timestamp functions” or something like that) and find the right way to achieve your desired result.

So basically you need to find out which SQL dialect you’re using and then search on Google for its documentation. Your database admin should know which dialect your database is using.

I hope this helps.

Warmly,