sql: problems with time.Time

[Archos]

time.Time is not working in drivers where I've tested:

  "github.com/Go-SQL-Driver/MySQL"
  "github.com/bmizerany/pq"
  "github.com/mattn/go-sqlite3"

You can see in line 81 and 85 that I'm using 2 different ways to get the value related to time.Time where be scanned (in line 155).
Which way would be the correct one to use? (Although it doesn't works of any way)

+ Using Args1(), I get the next errors:
MySQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: []uint8 -> *time.Time

PostgreSQL: got different data
input:  {0 2009-11-10 23:00:00 +0000 UTC}
output: {0 0001-01-01 00:00:00 +0000 UTC}

SQLite: sql: Scan error on column index 1: unsupported driver -> Scan pair: string -> *time.Time

+ Using Args2():

MySQL: got different data
input:  {0 2009-11-10 23:00:00 +0000 UTC}
output: {0 0001-01-01 00:00:00 +0000 UTC}

PostgreSQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: time.Time -> *string

SQLite: got different data
input:  {0 2009-11-10 23:00:00 +0000 UTC}
output: {0 0001-01-01 00:00:00 +0000 UTC}

* * *

http://play.golang.org/p/C0nDgOfrUW

[brainman]

https://code.google.com/p/odbc/ does support time as far as I remember. What is your problem?

Alex

[Archos]

The problem is that Scan is not returning the correct value which was entered in the SQL table.
All test is in http://play.golang.org/p/C0nDgOfrUW , but resuming:

+ The datetime used is

  var datetime = time.Date(2009, time.November, 10, 23, 0, 0, 0, time.UTC)

  var INSERT = fmt.Sprintf("INSERT INTO times (id, datetime) VALUES(0, '%s')",
      datetime.Format(time.RFC3339))

+ To get data, I've tried 2 different ways to scan data there but (1) it fails in some drivers and/or (2) it looks that has not been impemented:

  type Times struct {
      Id       int
      Datetime time.Time
  }

  func (t Times) Args1() []interface{} {
      return []interface{}{&t.Id, &t.Datetime}
  }

  func (t Times) Args2() []interface{} {
      tt := t.Datetime.Format(time.RFC3339)
      return []interface{}{&t.Id, &tt}

[Julien Schmidt]

This is a known issue with Go-MySQL-Driver: https://github.com/Go-SQL-Driver/MySQL/issues/9

I'm considering to publish a new beta release in the next few days which adds supports for time.Time. However, this would inevitably change the output as a string, so that it break the MySQL specification.
It would be nice to let the driver to configure how to parse strings / []byte to time.Time.

[Julien Schmidt]

The biggest problem with this is, that TIME / DATE / DATETIME values in MySQL don't contain information about the timezone.
What if the timezone of the time.Time differs from the server's timezone? Should the driver convert it?
What timezone should the time.Time have when it is parsed from a TIME / DATE / DATETIME value? UTC? The server's timezone?
This is very likely to have unexpected results and probably leads to inconsistency.

[Kamil Kisiel]

The pq driver for Postgres returns the time as UTC for the "timestamp without timezone" data type. You can leave it up to the author of the program to set the timezone correctly instead of making assumptions.

[Archos]

Assumptions? If you were tested that code then you were checked by yourself that there are no suppositions, since time.Time is not working in anyone of those drivers.

Note, that I also had tested it using: "CREATE TABLE times (id integer, datetime timestamp with time zone)"
for driver for PostgreSQL.

[Archos]

I've changed the test to use instead: var datetime = time.Now().UTC()
but I follow getting the same issues.

http://play.golang.org/p/P3JLEdc09e

Could you test it using that code? (Comment the code related to drivers MySQL and PostgreSQL if you only want test SQLite3 (from line 100 until 127))

El domingo, 3 de marzo de 2013 01:00:46 UTC, Antoine Grondin escribió:

I've been using time.Time just a few seconds ago with go-sqlite3 and what I save in comes back the same way I've put it there.

I'm saving the time using time.Now().UTC(), if it matters.

[Archos]

Then, a possilbe solution for the driver MySQL would be to save time.Time using a SQL datatype for string.

[Kamil Kisiel]

You must be doing something wrong. time.Time definitely works with pq, I am using it in production code. The only surprise is that if you don't use "timestamp with timezone" you get the time offset from what you expect since the driver assumes UTC.

[Archos]

Could you run this test? It's ready to test only PostgreSQL
http://play.golang.org/p/o3kkJZROIH

I get these errors:

== Testing with Args1()
PostgreSQL: got different data
input:  {0 2013-03-03 10:04:23.809663567 +0000 UTC}

output: {0 0001-01-01 00:00:00 +0000 UTC}

== Testing with Args2()

PostgreSQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: time.Time -> *string

[Archos]

Could you test this code? It's only ready for SQLite3

http://play.golang.org/p/JLfol9f8eK

I get these errors:

== Testing with Args1()

SQLite: sql: Scan error on column index 1: unsupported driver -> Scan pair: string -> *time.Time

== Testing with Args2()
SQLite: got different data
input:  {0 2013-03-03 10:22:49.223431879 +0000 UTC}

output: {0 0001-01-01 00:00:00 +0000 UTC}

El domingo, 3 de marzo de 2013 01:00:46 UTC, Antoine Grondin escribió:

I've been using time.Time just a few seconds ago with go-sqlite3 and what I save in comes back the same way I've put it there.

I'm saving the time using time.Now().UTC(), if it matters.

On Saturday, March 2, 2013 8:16:26 AM UTC-5, Archos wrote:

[Julien Schmidt]

I'm working on an experimental branch with time.Time support right now and I'm not sure what to return for the MySQL default value of DATE ("0000-00-00")  and DATETIME  ("0000-00-00 00:00:00") fields.
Any suggestions?

On Sunday, March 3, 2013 5:17:06 PM UTC+1, gwenn...@gmail.com wrote:

Hello,

0) you insert a string value in a table.

1) you want to retrieve a time.Time but:

 - the driver don't know the destination type (only the source/persisted type),

 - the driver should not return a string but a byte[] (mattn's driver seems to ignore this rule) (see driver.Rows.Next documentation),

 - there is no converter from byte[]/string to time.Time (see convertAssign).

2) you want to retrieve a string but your code seems wrong (t is a copy of output in Args2).

I've tried to make the second case work (with the help of the Scanner interface): http://play.golang.org/p/F0NUrRszyZ

Regards.

[Archos]

Thanks! I filled an issue about it:
https://github.com/mattn/go-sqlite3/issues/42

El domingo, 3 de marzo de 2013 16:17:06 UTC, gwenn...@gmail.com escribió:

Hello,

0) you insert a string value in a table.

1) you want to retrieve a time.Time but:

 - the driver don't know the destination type (only the source/persisted type),

 - the driver should not return a string but a byte[] (mattn's driver seems to ignore this rule) (see driver.Rows.Next documentation),

 - there is no converter from byte[]/string to time.Time (see convertAssign).

2) you want to retrieve a string but your code seems wrong (t is a copy of output in Args2).

I've tried to make the second case work (with the help of the Scanner interface): http://play.golang.org/p/F0NUrRszyZ

Regards.

On Sunday, March 3, 2013 11:25:27 AM UTC+1, Archos wrote:

[Archos]

El domingo, 3 de marzo de 2013 18:27:34 UTC, Julien Schmidt escribió:

I'm working on an experimental branch with time.Time support right now and I'm not sure what to return for the MySQL default value of DATE ("0000-00-00")  and DATETIME  ("0000-00-00 00:00:00") fields.
Any suggestions?

In the type Time (http://golang.org/pkg/time/#Time) is commented:

"The zero value of type Time is January 1, year 1, 00:00:00.000000000 UTC. As this time is unlikely to come up in practice, the IsZero method gives a simple way of detecting a time that has not been initialized explicitly."

So, as suggestion, could be used "0001-01-01 00:00:00"

[Kamil Kisiel]

The problem with Args1 is that it's a method with a value receiver instead of a pointer receiver. You are sending a copy of output's fields to Scan and then checking for the results in output, which will be null. In the case of Args2 tt is a string, not a time.Time and so the scanning simply won't work, as indicated by the error message.

[Kamil Kisiel]

Sorry, I meant "which will be the zero value" instead of "which will be null"

[Archos]

You're right, I had to use a pointer receiver. Now, the test (http://play.golang.org/p/3nZU0tbG5A) shows:

MySQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: []uint8 -> *time.Time

PostgreSQL: got different data
input:  {0 2013-03-03 19:48:03.911890356 +0000 UTC}
output: {0 2013-03-03 19:48:03 +0000 WET}

SQLite: sql: Scan error on column index 1: unsupported driver -> Scan pair: string -> *time.Time

* * *

The issue that I see now is that PostgreSQL returns the value with a local time zone, instead of UTC.

[Julien Schmidt]

Using time.Time as a Query / Exec parameter does already work: https://github.com/Go-SQL-Driver/MySQL/blob/master/packets.go#L612.
Currently the time is sent as a string in the format "2006-01-02 15:04:05". The latest MySQL-Server versions are now able to store an optional time "fractal" in microseconds ("YYYY-MM-DD HH:MM:SS[.fractal]" so I'll try switching to an unix timestamp instead to archive the maximal precision without losing backwards compatibility.

The MySQL protocol defines, that DATE values are sent as bytes containing values for year, month day and DATETIME as year, month, day, hour, minute, seconds and an optional fractal: https://github.com/Go-SQL-Driver/MySQL/blob/master/packets.go#L854
So using http://golang.org/pkg/time/#Date should be the best option.

On 03.03.2013 19:25 UTC, Archos wrote:

Hi Julien,

"Then, a possilbe solution for the driver MySQL would be to save time.Time using a SQL datatype for string."

Another option would be to store it like an int 64 using time.Unix (http://golang.org/pkg/time/#Time.Unix).
Then, at Scan, it is recovered the type Time using time.Unix (http://golang.org/pkg/time/#Unix)

The advantage will be the size, the con. is the conversion from int64 to Time

El domingo, 3 de marzo de 2013 00:15:26 UTC, Julien Schmidt escribió:

[Julien Schmidt]

Sorry, this is true only for the binary protocol (prepared statements). In the text protocol everything is sent as a string: https://github.com/Go-SQL-Driver/MySQL/blob/master/packets.go#L477

[Kamil Kisiel]

Have a read of http://www.postgresql.org/docs/8.4/static/datatype-datetime.html, particularly 8.5.1.3

The Postgres server stores all timestamps as UTC internally and converts the output to the locally configured time zone when returning it. You'll need to use (*time).In to convert the return value to the input timezone.

[Archos]

https://github.com/bmizerany/pq/issues/84

[Tor Langballe]

I've just been struggling with this using sqlite:

github.com/mattn/go-sqlite3

I'm not sure about inserting time as time.Time into the sqlite3 database, but if you insert it as text, make sure it doesn't have a timezone at the end (and convert it to UTC most likely).

I'm using:   stime = settings.Updated.UTC().Format("2006-01-02T15:04:05.999999999") 

Likewise, you need to declare it as TIMESTAMP (not TEXT TIMESTAMP) when creating the table.

Once I did this, I could scan the column as to time.Time

[Antoine Grondin]

Hey sorry for not coming back to you earlier.

To save the time.Time into SQLite, you need to use the TIMESTAMP datatype.

CREATE TABLE IF NOT EXISTS Posts(
   -- ...
   datetime TIMESTAMP,
   -- ...
)

Then when you read the value or write it, you use the time.Time type:

type Post struct {
	// ...
	datetime    time.Time
        // ...
}

When you set the time in the code, save it as UTC such that you don't need to worry about timezones when you get it back.

func NewPost(author, content string) *Post {
	// ...
	p.datetime = time.Now().UTC()
        // ...
	return p
}

And then when you want to query it back from SQLite, using a prepared statement I did it such as:

var author string
var content string
var datetime time.Time
err = stmt.QueryRow(id).Scan(&author, &content, &datetime)

And everything works just fine.  I do it here: https://github.com/aybabtme/goblog-prototype/blob/master/db/post.go

I'm much of a noob, I've been playing around with Go on weekends so my code might not be of top quality... however I don't have that problem you report with go-sqlite3.

[Archos]

When I was reading about the data types for SQLite3, I didn't found anything about TIMESTAMP. In fact, the doc. says in http://sqlite.org/datatype3.html:

* * *

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

* * *

How did you know about that type?
By the way, I just confirm that it works when it's passed a time without time zone. Thanks!

[Archos]

I don't know if it is a good practice because the SQL code would not be compatible with drivers in other languages that have been the specification.

El sábado, 9 de marzo de 2013 17:30:06 UTC, gwenn...@gmail.com escribió:

The "timestamp" type is hardcoded in the driver:

https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L350

https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L377

Regards.

[Archos]

https://github.com/mattn/go-sqlite3/issues/44

[RickyS]

So what is the best way to use MySQL times with Go time.Time these days with go-sql-driver ?

My DSN is now a result of some study and guesswork:
   "root:@/simpleuser?charset=utf8&parseTime=true"

My current plan is to store a TimeZone field in the database when needed, but to store UTC in the MySQL datetime.

Working examples would be appreciated.

[Arne Hormann]

If you have a reasonably recent driver (1.1+), you can drop "charset=utf8", it's the default.

The best way is to only store UTC values.

If you need to display timezone specific dates, it's best to convert them in the client.

If you are ok with a string and don't need the time operations, you can get by without "parseTime=true" and read the column into a []byte or a string.

If you don't use UTC (the default for the driver) on your database server, specify your timezone with "loc=TZ", where TZ is urlencoded ("loc=US%2FPacific" for US/Pacific).

GMT, CET etc. won't work, the driver directly relays this parameter to time.Date after url decoding. The rules of the time package apply.

If your database is on the same computer as the client, you can use "loc=Local".

That's about it, there's a lot of options and you have to pick them depending on your problem.