The different SQL databases may have common elements but they all have different way of storing data internally

This is most certainly true when date and time is stored. For instance SQLite does not have a date or time type just TEXT or NUMBER. MySQL has the DATETIME type. Postgres has TIMESTAMPTZ(6).

In view of this retrieving a value of time.Time and *time.Time can be quite problematic with Golang. Especially if you use an ORM, and the ORM has decided its not so important to implement this feature agnostic of the database engine implementation. Looking at you GORM :)

See the only reason one would use an ORM instead of native SQL is to be able to use multiple datastores interchangeably. For instance do your test with in memory database like SQLite on your local machine or Github actions to keep them fast, and run production on Postgres, MySQL, or SQL Server

Then you find yourself fighting incomprehensible errors like this:
MySQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: []uint8 -> *time.Time
SQLite: sql: Scan error on column index 1: unsupported driver -> Scan pair: string -> *time.Time
PostgreSQL: sql: Scan error on column index 1: unsupported driver -> Scan pair: time.Time -> *string

Searching the internet does not come with anything helpful.
Dubious advices like this one: "So you need to use string instead of time.Time . And parse the string". Why should you use strings when your database supports datetime.

Or this one "The driver is working with time.Time, but you need to declare the column as TIMESTAMP (not TEXT TIMESTAMP) when creating the table". Of couse your gut feeling says you its wrong. Why should I declare my field as timestamp when SQLite does not support it.

Or "Are you using the database.sql Driver implementation or the "direct" layer ?" - like what the heck is THE "direct" layer.

Or "lib/pq implements a NullTime type for that, define a scanner interface for a time.Time type that may be null and use it in exchange to *time.Time." Really, I just want to use my time.Time man.

There must be a better way. Always listen to your gut feeling.

Ok, ok. Here is the example of how this can be achieved without an ORM and agnostic of the database engine.

sqlStr, _, _ := goqu.From("table_1").Where(goqu.C("entity_id").Eq(attrID)).Select("id", "name", "created_at", "updated_at", "deleted_at").ToSQL()

var createdAt string
var updatedAt string
var deletedAt *string

var attr Attribute{}
err := db.QueryRow(sqlStr).Scan(&attr.ID, &attr.Name, &createdAt, &updatedAt, &deletedAt)
if err != nil {
    if err == sql.ErrNoRows {
	return nil, nil
   }
   return nil, err
}

layout := "Mon Jan 02 2006 15:04:05 GMT-0700"
createdAtTime, err := time.Parse(layout, createdAt)
if err == nil {
	attr.CreatedAt = createdAtTime
}
updatedAtTime, err := time.Parse(layout, updatedAt)
if err == nil {
	attr.UpdatedAt = updatedAtTime
}
if deletedAt != nil {
    deletedAtTime, err := time.Parse(layout, *deletedAt)
    if err == nil {
	attr.DeletedAt = &deletedAtTime
    }
}

As you can see I am using GOQU here. GOQU is a query builder for Golang, which can build your query with any SQL dialect you may need. This allows you to change or swap your database at any point.

In this example you have a type Attribute with CreatedAt and UpdatedAt required fields. And an optional DeletedAt field to be used for soft deletes.

The CreatedAt and UpdatedAt fields are pretty sytaight forward. This is because they will always be NOT NULL values (as long as you have set these as a database constraint as you should)

The most problematic field is the optional DeletedAt field. The reason is it can be coming as NULL from the database. Which is why there is an extra check that the field is not nil, before parsing it to a time.Time data type

With the above in place we have achieved the following. We have successfully converted our database data to GoLang types regardless of how it is stored inernally (i.e. strings). We have kept our code agnostic, and can switch between SQLite, Postgres, MySQL or SQLServer in a blink of an eye.