Calendar-Day Flooring Is Not 24-Hour Truncation in Go

Adityo Guni WaluyoOctober 13, 202604:42

time.Truncate cuts by duration since the zero time, not to local midnight. Here is the safe pattern for DATE columns.

TL;DR

Go's time.Truncate(24 * time.Hour) counts multiples from Go's zero time, not local midnight, so early-morning timestamps on a UTC+7 server slip to the previous day. The MySQL driver's default UTC location conversion then locks the wrong day in. Fix: extract date components and rebuild with time.Date at UTC midnight.

Why Calendar-Day Flooring Is Not 24-Hour Truncation in Go

While filtering an admin list by a date range on a rental management project, I saw one submission recorded on the wrong day. A submission that should have landed on the 15th showed up under the 14th in the filter results. That mismatch made the rental schedule inaccurate: the start of the period became over-inclusive and the end under-inclusive once the period-overlap filter was applied.

My first guess was a server timezone misconfiguration. I assumed that cutting the time down to local midnight with time.Truncate(24 * time.Hour) was enough to get the right calendar date before saving into a DATE column.

That assumption was wrong. The fact is that time.Truncate does not round down to local midnight. It rounds down to a multiple of a duration measured from Go's zero time [1]. On a UTC+7 server, any local timestamp between 00:00 and 06:59 gets pulled back to the previous day.

The driver makes it worse. go-sql-driver re-zones every time.Time value into its configured location (cfg.Loc) when serializing, through v.In(mc.cfg.Loc) inside writeExecutePacket [2]. The DSN default location is UTC [3], so a local midnight produced by a UTC+7 process gets converted again, and the wrong day is locked into the database.

The Root Cause in Time Truncation

time.Truncate is often misread as a business-day start helper. It is really a low-level duration operation, not a human-calendar one. Go's zero time is January 1, year 1, 00:00:00 UTC. Cutting by 24 hours finds multiples of 24 hours from that absolute point, not the day boundary in the user's local zone.

When the app runs at a positive offset like UTC+7, 02:00 local equals 19:00 the previous day in UTC. Applying time.Truncate(24 * time.Hour) before any conversion therefore references the previous day's UTC midnight. That is why anything entered in the early morning is systematically recorded as the previous day.

The Correct Fix Pattern

The fix is to stop using duration arithmetic for calendar dates. Extract the year, month, and day from the parsed timestamp, then rebuild the value at UTC midnight.

Here is the wrong and right approach inside an internal scheduler function like aggregateSchedule:

// Wrong approach: raw duration math
func getWrongDate(t time.Time) time.Time {
    // This will fail for 00:00 - 06:59 on a UTC+7 server
    return t.Truncate(24 * time.Hour)
}

// Correct approach: calendar component extraction
func getCorrectDate(t time.Time) time.Time {
    y, m, d := t.Date()
    // Rebuild explicitly at UTC midnight
    return time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
}

By using time.Date with time.UTC set explicitly, the day stored in the database is the UTC calendar day equivalent of the user's local calendar day. The driver no longer performs a risky zone conversion, because the value already sits at UTC midnight.

Verifying at the Integration Level

This change cannot be verified with a unit test alone, because the function is unexported and the derived dates only become observable once they reach the database through the connection layer.

So the regression test belongs at the integration tier. The test sets up a database connection with the default location configuration, inserts through the fixed path, and reads the value back. Simulating input at 03:00 local (UTC+7) and asserting that the stored date equals the local input date, not the previous day, is what locks the behavior in.