TIL: Becareful on Postgres Query, for Less than Or Equal on Timestamp
Tags: postgres, softwareengineering, todayilearned, pagination
Published: 2022-01-24 09:24:21

---
title: TIL: Becareful on Postgres Query, for Less than Or Equal on Timestamp
published: true
date: 2020-05-26 04:21:09 UTC
tags: postgres,softwareengineering,todayilearned,pagination
canonical_url: https://medium.com/easyread/til-becareful-on-postgres-query-for-less-than-or-equal-on-timestamp-9e486b657fc
---



![](https://cdn-images-1.medium.com/max/1024/0*-9RkFzLnxCdtsFwt)<figcaption>Photo by <a href="https://unsplash.com/@jonasjacobsson?utm_source=medium&amp;utm_medium=referral">Jonas Jacobsson</a> on <a href="https://unsplash.com?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption>


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.

{% gist https://gist.github.com/bxcodec/973374104cd1032db50667ebab29e34e %}

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.

{% gist https://gist.github.com/bxcodec/91ff9fde11ab082ba67335595bf81265 %}

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,

{% gist https://gist.github.com/bxcodec/61a4798688e6d3dc3a324ea5205751bb %}

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](https://golang.org/pkg/time/#pkg-constants)

![](https://cdn-images-1.medium.com/max/1024/1*gSd1st48nYVav5TKkkWaxA.png)<figcaption>time format in Golang package</figcaption>

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.

{% gist https://gist.github.com/bxcodec/5e09b0c1a2cd1ba72c306966821b2ae0 %}

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](https://stackoverflow.com/questions/39119783/postgres-using-timestamps-for-pagination) — Stackoverflow (on the comment’s thread)
<iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fc36271%3Fas_embed%3Dtrue&amp;dntp=1&amp;display_name=Upscribe&amp;url=https%3A%2F%2Fupscri.be%2Fc36271&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/1f83e4a733ad3206f47e6dd38aa4fc6d/href">https://medium.com/media/1f83e4a733ad3206f47e6dd38aa4fc6d/href</a></iframe>
* * *