Precision of TIMESTAMP type is different

[naoina]

On cloud-spanner-emulator, the following SQL will get the error spanner: code = "OutOfRange", desc = "Invalid timestamp: '2022-03-31T23:59:59.999999999Z'"

SELECT TIMESTAMP('2022-03-31T23:59:59.999999999Z');

However, Cloud Spanner does not return any error.

Also the emulator accept nanoseconds level value as TIMESTAMP type, but MAX() and MIN() returns microseconds's one.
For example, if SomethingTable has CreatedAt TIMESTAMP NOT NULL column and value of 2022-03-31T14:59:59.999999999Z was inserted.
The following SQL returns 2022-03-31T14:59:59.999999999Z.

SELECT CreatedAt FROM SomethingTable

However, the following SQL returns 2022-03-31T14:59:59.999999Z.

SELECT MAX(CreatedAt) FROM SomethingTable;

[naphatkrit]

any updates on this issue? we're also running into this.

[krzyjab]

I'm facing the same issue. Interestingly, this seems to be working correctly in the Spanner Emulator:
SELECT PARSE_TIMESTAMP("%FT%H:%M:%E*S%Ez", "2022-10-11T11:47:07.078142285Z");
and this doesn't:
SELECT TIMESTAMP("2022-10-11T11:47:07.078142285Z");

[ebkn]

I'm facing the same issue.
This problem happens on gcr.io/cloud-spanner-emulator/emulator:latest on GitHub Actions ubuntu-22.04 machine.
But it works correctly on gcr.io/cloud-spanner-emulator/emulator:latest on my local machine (m1 MacBook Pro).
Maybe this problem is related to architecture.

[gauravpurohit06]

We are looking into it and will back soon.

[ThePersonThat]

any updates? I faced the same issue

[kberezin-nshl]

Root cause is in ZetaSQL reference implementation, when calling TIMESTAMP(string) function, they ignore nanosecond precision option which Spanner Emulator sets.

There is a workaround though:

SELECT CAST('2022-03-31T23:59:59.999999999Z' AS TIMESTAMP)

totally works

[kberezin-nshl]

I raised a pull request which fixes TIMESTAMP() function: #160

[egonelbre]

Hit the same issue. Here's a small reproducer:

DROP TABLE IF EXISTS Demo;
CREATE TABLE Demo (
    Id   INT64     NOT NULL,
    Time TIMESTAMP NOT NULL
) PRIMARY KEY (Id);

INSERT INTO Demo (Id, Time) VALUES (1, TIMESTAMP '2024-10-16 04:03:54.939599300-00');

SELECT * FROM Demo WHERE Time = (SELECT MAX(Time) FROM Demo);
-- result is empty

SELECT STRING(MAX(Time)) FROM Demo;
-- result is "2024-10-15 21:03:54.939599-07"