How do I query sql.NullTime value from sql in golang stored in the format 2023-06-08 19:54:41 +0000

Question

    currentTime := time.Now().UTC().Format(layout)
    prevTime := time.Now().UTC().Add(-time.Minute * 15).Format(layout)
    currTime, _ := time.Parse("2006-01-02 15:04:05", currentTime)
    previousTime, _ := time.Parse("2006-01-02 15:04:05", prevTime)
    query := `
        SELECT *
        from %s
        where
        updated_on_utc > %v
        and
        updated_on_utc <= %v`
    query = fmt.Sprintf(query, TableName, previousTime, currTime)
    data := db.DbClient.ExecuteQuery(query)

this gives out the error message : panic: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19:54:41 +0000 UTC and updated_on_utc <= 2023-06-08 20:09:41 +0000 UTC' at line 4

I have tried different queries with different placeholders but all of them give the same error

Answer 1

From the code you mentioned here, I'm understanding that the column updated_on_utc is of type text(time.Parse function is used to retrieve the text), you have to change the query to the following:

SELECT *
    from %s
    where
    updated_on_utc > "%v"
    and
    updated_on_utc <= "%v"

Please don't use string formatters(Why should we use query parameters?), you need to use query parameters like below:

    SELECT *
    from %s
    where
    updated_on_utc > ?
    and
    updated_on_utc <= ?

Then change your code as below:

query = fmt.Sprintf(query, "your_table_name")
_, err = db.Query(query, previousTime, currTime)

As the time is stored in DB as string, the query won't return the desired result. So you need to change the type to datetime or timestamp.