pythonsqlitepandasql

Hi everyone, I have some troubles running an easy sql query using pandasql

...
q='SELECT EXTRACT(HOUR FROM dTime) as hour FROM data'
output=pandasql.sqldf(q,globals())

(column dTime contains time-data as e.g. '2019-02-15 03:44:27')

It doesn't work due to this error:

OperationalError: near "FROM": syntax error

I'm not able to understand how to solve it, could you help me?

Solution

  • SQLite does not support the function extract(), but you can use strftime():

    q = "SELECT strftime('%H', dTime) AS hour FROM data"

    this returns the hour as a string in the format hh.

    If you want the hour as a number, then:

    q = "SELECT strftime('%H', dTime) + 0 AS hour FROM data"

    By adding 0 to the result of strftime() it is implicitly converted to a number.