Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to parse time from database

Tags:

database

mysql

go

I am using golang and I am trying to read time from mysql and I am getting the following error.

var my_time time.Time rows, err := db.Query("SELECT current_time FROM table") err := rows.Scan(&my_time) 

The error I am getting is

 unsupported driver -> Scan pair: []uint8 -> *time.Time 

How can I fix this?

like image 811
Caffeinatedwolf Avatar asked Mar 30 '15 08:03

Caffeinatedwolf


People also ask

What is PARSE time in SQL?

The PARSE() function returns the result of an expression, translated to the requested data type in SQL Server. So you can use it to “translate” your string value into a date/time data type (such as date, datetime, datetime2, etc).

What does PARSE date do in SQL?

parseDate parses a string to determine if it contains a date value, and returns a standard date in the format yyyy-MM-ddTkk:mm:ss.

How do I do a timestamp in SQL?

MySQL TIMESTAMP() FunctionThe TIMESTAMP() function returns a datetime value based on a date or datetime value. Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.

Can you PARSE in SQL?

The SQL PARSE function is a Conversions Function used to convert the String data to the requested data type and returns the result as an expression. It is recommended to use this SQL PARSE function to convert the string data to either Date time, or Number type.


1 Answers

Assuming you're using the go-sql-driver/mysql you can ask the driver to scan DATE and DATETIME automatically to time.Time, by adding parseTime=true to your connection string.

See https://github.com/go-sql-driver/mysql#timetime-support

Example code:

db, err := sql.Open("mysql", "root:@/?parseTime=true") if err != nil {     panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic } defer db.Close()  var myTime time.Time rows, err := db.Query("SELECT current_timestamp()")  if rows.Next() {     if err = rows.Scan(&myTime); err != nil {         panic(err)     } }  fmt.Println(myTime) 

Notice that this works with current_timestamp but not with current_time. If you must use current_time you'll need to do the parsing youself.

This is how you do custom parsing:

First, we define a custom type wrapping []byte, that will automatically parse time values:

type rawTime []byte  func (t rawTime) Time() (time.Time, error) {     return time.Parse("15:04:05", string(t)) } 

And in the scanning code we just do this:

var myTime rawTime rows, err := db.Query("SELECT current_time()")  if rows.Next() {     if err = rows.Scan(&myTime); err != nil {         panic(err)     } }  fmt.Println(myTime.Time()) 
like image 141
Not_a_Golfer Avatar answered Sep 22 '22 12:09

Not_a_Golfer