TIL: Becareful on Postgres Query, for Less than Or Equal on Timestamp

Photo by Jonas Jacobsson on Unsplash

So a week ago, I do some experiments related to pagination on Postgres with combined UUID and timestamp. I got an interesting problem here, it’s happened when I’m doing a query for my created timestamp on the same timestamp.

Context

To give some context, I have a database schema like this.

And out of curiosity, I’m doing a load testing on my application, with many concurrent users up to 100 concurrent inserts. And this caused my database to have a lot of rows that have the same timestamp. For example can be seen below.

As you can see, the created_time was the same for all the records (may up to 100 records).

Problems

So, when I’m doing query based on the created time, there’s weird behavior, I thought it was a bug or something.

SELECT \* FROM payment\_with\_uuid 
WHERE 
created\_time **<= '2020-05-24 21:27:10'**
ORDER BY created\_time DESC LIMIT 10;

So, from that query, what I want to achieve is, I want to select all record that has the created_time less than or equal to (<=)the given timestamp.

But what I got instead is only all the timestamp that less than that created timestamp that is filtered. The timestamp that has the same value is not filtered.

So from this example rows below,

The result that I got is only Kane but Allistair and James is not filtered.

Another weird thing is if I reverse the query, like using greater than equal to (>=) syntax, I can fetch the others like Allistair and James but Kane will be out of order obviously.

Solutions and Things that I just Learned

The timestamp is an integer UNIX-timestamp under the hood

So, after searching on the whole internet, and Stackoverflow obviously, even asking my friend, I got the answer, that basically, the timestamp that stored in the database is UNIX-timestamp under the hood.

For example, if you see the database records from the above example, the stored timestamp is like this.

**2020-05-24 21:27:10**

Actually this is not the real value, because Postgres actually stores the UNIX-timestamp version of that rows. It may have nano or microseconds, so it’s not only stopped on the seconds version.

It may look like this,

**2020-05-24 21:27:10.37091**

We don’t know. Because for the sake of formatting, Postgres round it only to the second value.

So, I change my query to more specific, including the nano or microseconds, into looks like this, and which is works.

SELECT \* FROM payment\_with\_uuid 
WHERE 
created\_time **<= '2020-05-24 21:27:10.37091****'**
ORDER BY created\_time DESC LIMIT 10;

But then, there’s a question, how to do this from the application level. But for my case, since I’m using Golang, it’s quite easy.

Using RFC3339Nano On Golang

Since my application is built on top of Golang, to handle including the precision, I use time.RFC3339Nano from the time package in Golang. Its format looks like this, "2006–01–02T15:04:05.999999999Z07:00". Or you can see the details here

time format in Golang package

I don’t know how to do it in other programming languages, but the key is to include the precision as well for the query on the timestamp.

So in the case of doing a query that accepting the timestamp from the end-user, we need to format it first using the RFC3339Nano, and send it to the database. So the query still valid.

But why I need to use format, if I can use it without format? Because if we don’t format it, the time data struct will contain the milliseconds and nanoseconds as well? So to give the context, why I need this, especially in my case.

So I will use the created_time for pagination to fetch the next page as a cursor, I will get that timestamp, and convert it to a string. And that string will be used by the user for fetching a new page as a cursor.

And in my application, I will convert that string back to timestamp and use it for the query. And because of that, we need to do it carefully. Since in Golang, when we convert the timestamp to a string, it might not include the precision that we got from the Postgres. That’s why I need to format it with time.RFC3339Nano.

So I still keep maintaining the precision even I convert it to string and revert it back to timestamp.

References

1. Postgres: using timestamps for pagination — Stackoverflow (on the comment’s thread) https://medium.com/media/1f83e4a733ad3206f47e6dd38aa4fc6d/href * * *