make_timestamp/make_timestamptz returns invalid timestamp when seconds < 1

[Informatic]

Describe the problem

make_timestamp and make_timestamptz seem to return invalid date (years in the past) when sec argument is lower than 1

To Reproduce

What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

1. Set up CockroachDB cluster
2. Send SQL: select make_timestamp(1, 1, 1, 0, 0, 0);
3. Look at result:

         make_timestamp
---------------------------------
  0293-09-22 00:12:43.145224 BC
(1 row)

Expected behavior

    make_timestamp
-----------------------
  0001-01-01 00:00:00
(1 row)

Environment:

• CockroachDB version v23.1.23
• Server OS: Docker
• Client app: cockroach sql

Additional context
Issue seemingly stems from modulo by zero in implementation which returns NaN, which then is casted to int (resulting in a large negative number).

This should be solved with something along the lines of:

truncatedSec := math.Floor(sec)
var nsec float64
if truncatedSec == 0 {
  nsec = sec * float64(time.Second)
} else {
  nsec = math.Mod(sec, truncatedSec) * float64(time.Second)
}
t := time.Date(year, month, day, hour, min, int(truncatedSec), int(nsec), location)

Jira issue: CRDB-40113

Epic CRDB-43310

[blathers-crl]

Hi @Informatic, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

@Informatic Thanks for the bug report!

@Dedej-Bergin I'm assigning this to you since you are currently on-call. It looks like we should be able to fix this by handling the edge case a little more carefully.