When to Use the `timeTruncate` DSN Parameter in go-sql-driver/mysql

The timeTruncate DSN parameter rounds down every time.Time value to a specified duration before sending it to MySQL, preventing precision mismatches when your database columns store time with lower granularity than Go's nanosecond precision.

The go-sql-driver/mysql driver provides the timeTruncate configuration option to control how temporal data is transmitted to the server. When working with MySQL DATETIME or TIMESTAMP columns that do not store fractional seconds, this parameter ensures that the values sent by your Go application match exactly what MySQL will store, eliminating subtle bugs caused by nanosecond truncation.

What Is the timeTruncate DSN Parameter?

timeTruncate is a duration-based configuration that accepts values like 1s, 1m, or 1h. When set, the driver applies time.Time.Truncate(d) to every timestamp parameter before encoding it for the MySQL wire protocol.

The truncation occurs in utils.go within the appendDateTime function (lines 68–71), which is the low-level formatter responsible for building binary DATETIME packets. This ensures the rounding happens consistently across all query types—prepared statements, direct Exec calls, and Query operations.

Why Use timeTruncate?

Avoid Precision Mismatches with MySQL DATETIME

MySQL DATETIME columns without explicit fractional precision (e.g., DATETIME instead of DATETIME(6)) store values only to the second. Go's time.Time carries nanosecond precision. When you insert time.Now() and later retrieve it, the returned value lacks the original sub-second data, causing equality checks to fail.

Setting timeTruncate=1s ensures the value sent to MySQL matches exactly what MySQL will return, making value == retrievedValue evaluate to true.

Prevent Silent Data Loss Surprises

Without truncation, the driver sends nanoseconds to MySQL, which silently drops them. This creates a discrepancy between the Go value in memory and the persisted value. By configuring timeTruncate, you make this precision loss explicit and predictable at the application layer rather than the database layer.

Reduce Network Traffic

Stripping sub-second information reduces the byte size of temporal data transmitted over the wire. While modest per row, this optimization becomes significant in high-throughput applications processing millions of timestamped events.

How timeTruncate Works in the Source Code

The implementation spans several files in the repository:

Configuration Parsing (dsn.go, lines 602–606):

cfg.timeTruncate, err = time.ParseDuration(value)

This parses the DSN string value (e.g., "1s") into a time.Duration.

Functional Option (dsn.go, lines 113–120):

func TimeTruncate(d time.Duration) Option {
    return func(cfg *Config) error {
        cfg.timeTruncate = d
        return nil
    }
}

Allows programmatic configuration via mysql.TimeTruncate(time.Second).

Truncation Logic (utils.go, lines 68–71):

if timeTruncate > 0 {
    t = t.Truncate(timeTruncate)
}

The actual rounding occurs in appendDateTime before the binary packet is constructed.

Query Execution (connection.go, line 301; packets.go, line 1211):

buf, err = appendDateTime(buf, v.In(mc.cfg.Loc), mc.cfg.timeTruncate)

The configured duration is passed through during parameter encoding for both text and binary protocols.

Practical Configuration Examples

DSN String Configuration

// Truncate to seconds (remove milliseconds/microseconds/nanoseconds)
dsn := "user:password@tcp(127.0.0.1:3306)/dbname?parseTime=true&timeTruncate=1s"
db, err := sql.Open("mysql", dsn)

Effect: Any time.Time value passed to db.Exec() or db.Query() will have its sub-second component zeroed out before transmission.

Programmatic Configuration with TimeTruncate Option

cfg := mysql.NewConfig()
cfg.User = "user"
cfg.Passwd = "password"
cfg.Net = "tcp"
cfg.Addr = "127.0.0.1:3306"
cfg.DBName = "dbname"
cfg.ParseTime = true

// Apply truncation to the nearest minute
if err := cfg.Apply(mysql.TimeTruncate(time.Minute)); err != nil {
    log.Fatal(err)
}

db := sql.OpenDB(mysql.NewConnector(cfg))

Effect: Timestamps are rounded down to the start of the minute (e.g., 15:04:05 becomes 15:04:00).

Verifying the Truncation Effect

t := time.Date(2024, 3, 2, 15, 4, 5, 123456789, time.UTC)

// Insert with timeTruncate=1s configured
_, err := db.Exec("INSERT INTO events (created_at) VALUES (?)", t)
if err != nil {
    log.Fatal(err)
}

// Retrieve the value
var stored time.Time
err = db.QueryRow("SELECT created_at FROM events LIMIT 1").Scan(&stored)
if err != nil {
    log.Fatal(err)
}

fmt.Printf("Original: %v\n", t)      // 2024-03-02 15:04:05.123456789 +0000 UTC
fmt.Printf("Stored:   %v\n", stored) // 2024-03-02 15:04:05 +0000 UTC

The stored value matches the truncated original, ensuring consistency between what Go sends and what MySQL stores.

Summary

• timeTruncate rounds down time.Time values to a specified duration before transmission to MySQL.
• Configure it via DSN (timeTruncate=1s) or programmatically (mysql.TimeTruncate(time.Second)).
• The truncation occurs in utils.go (appendDateTime, lines 68–71) and applies to all query parameters.
• Use it to prevent precision mismatches with non-fractional DATETIME columns, avoid silent data loss, and ensure equality between inserted and retrieved values.
• The original Go time.Time value remains unmodified; only the transmitted copy is truncated.

Frequently Asked Questions

Does timeTruncate modify the original Go time.Time value?

No. The driver operates on a copy of the timestamp during the encoding phase in appendDateTime (utils.go). The original variable you pass to db.Exec() or db.Query() retains its full nanosecond precision after the call returns.

What duration values are valid for timeTruncate?

Any valid Go time.Duration string accepted by time.ParseDuration, such as 1ns, 1us (or 1µs), 1ms, 1s, 1m, or 1h. The value is parsed in dsn.go (lines 602–606). Durations larger than the precision of your MySQL column (e.g., 1h for a DATETIME column) are valid but may zero out more precision than necessary.

Does timeTruncate affect time values read from MySQL?

No. The parameter only affects values sent to MySQL (parameters in INSERT, UPDATE, WHERE clauses, etc.). Values retrieved from MySQL are parsed according to the column type and the parseTime DSN setting. If you need to truncate retrieved values, you must call Truncate() in your application code after scanning.

Can I use timeTruncate with DATETIME(6) columns?

Yes, but it is usually unnecessary. DATETIME(6) stores microseconds, so truncating to 1s would discard the fractional seconds that the column can store. However, if your application logic requires specific precision (e.g., minute-level timestamps in a DATETIME(6) column), you can use timeTruncate=1m to ensure all writes conform to that granularity despite the column's capability for higher precision.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}