[Solved] Reading a datetime from a sqlite3 database

Getting Help
1

I just started using Go, and now I am trying to get a datetime out of a Sqlite3 database. In this case I am trying to read the field added.

videos_table

I am using the code below. I know that the record with the specific video_id exists, it works fine if I try to read an integer or string from the same table, but not when I am trying to read a datetime. I get back 0001-01-01 00:00:00 +0000 UTC everytime, instead of the date in that field? My guess is that I am doing something really stupid here, but I don’t see it :slight_smile: Anyone knows how to deal with dates and Sqlite?

package database

import (
	"database/sql"
	"fmt"
	"time"

	entities "github.com/hultan/softtube/softtube.entities"
)

const databasePath = "/home/per/temp/test.db"
const connectionString = "file:" + databasePath + "?parseTime=true"
const sqlStatementGetDate = "select added from Videos where video_id='wacVZa4DQ6g'"

// GetDate : Get the added field
func GetDate() (*time.Time, error) {
	// Open database
	db, err := sql.Open(driverName, connectionString)
	if err != nil {
		return nil, err
	}

	rows, err := db.Query(sqlStatementGetDate)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var added time.Time
	if rows.Next() {
		if err = rows.Scan(&added); err != nil {
			panic(err)
		}
	}

	fmt.Println(added) // Prints : 0001-01-01 00:00:00 +0000 UTC

	// Return the date
	return &added, nil
}
2

Please do a print in the if to see if really an entry has been found.

3

Do you mean:
fmt.Println(rows.Next())
before the row
if rows.Next() {
?

In that case, yes, it prints true…

4

One thing that I am thinking of is how to get the import
_ "github.com/mattn/go-sqlite3"
into the code. If I add it and save the file, the line is removed. I guess I’ll have to use it somewhere, but I am not sure how. It doesn’t seem to be necessary for the code to work (for strings and integers) but shouldn’t it be needed?

Ignore this question, apparently I had this import in another file. I guess imports works across the entire package or something. I’ll investigate that later…

5

No, I meant to print an arbitrary string inside. You shouldn’t call next without actually consuming the row. It will advance the cursor needlessly.

Otherwise hard to say.

Can you provide a minimal project with an example dataset on GitHub that demonstrates the problem?

6

Here is an example project:

7

Found a solution to the problem, I read the date as a string instead and parse the date just before leaving the GetDate() function. Probably not the best solution but at least it lets me continue on to other problems:

// Change 1 : Convert the date to a varchar first
const sqlStatementGetDate = "select cast(added as varchar) from Videos where video_id='wacVZa4DQ6g'" 

// GetDate : Get the date
func GetDate() (*time.Time, error) {
	// Open database
	db, err := sql.Open(driverName, connectionString)
	if err != nil {
		return nil, err
	}

	rows, err := db.Query(sqlStatementGetDate)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

       // Change 2 : Read the value as a string
	var added string

	if rows.Next() {
		if err = rows.Scan(&added); err != nil {
			panic(err)
		}
	}

        // Change 3 : Parse the string
	layout := "2006-01-02T15:04:05-0700"
	date, err := time.Parse(layout, added)

	// Return the date
	return &date, nil
}
8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.