Solving PostgreSQL Timestamp Problems: Adding Intervals to JSONB Timestamps

Learn how to effectively handle date arithmetic with PostgreSQL JSONB arrays, specifically when adding intervals to timestamps in milliseconds.
---
This video is based on the question https://stackoverflow.com/q/64703237/ asked by the user 'lukasz-p' ( https://stackoverflow.com/u/6549154/ ) and on the answer https://stackoverflow.com/a/64703455/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: add interval to timestamp with milis from jsonb

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving PostgreSQL Timestamp Problems: Adding Intervals to JSONB Timestamps

In today's post, we will delve into a common geometric problem that many developers face when working with PostgreSQL: handling timestamps that are stored in different formats within a JSONB array. Specifically, we’ll look at adding intervals to timestamps expressed in milliseconds and resolving any difficulties related to data type casting.

The Challenge

Imagine you have a PostgreSQL database with a JSONB column containing an array of objects, where each object has two timestamps: effectiveFrom and effectiveTo. These timestamps are stored as bigint in milliseconds. The core requirement is to check the following:

If effectiveFrom is greater than the current time minus 7 days.

If effectiveFrom + 1 year is less than effectiveTo.

However, you encounter obstacles due to mixed data types—effectiveFrom is a bigint while effectiveTo is a string. Not to mention, you are dealing with casting errors such as "cannot cast type timestamp without time zone to bigint".

Proposed Solution

To effectively manage date arithmetic operations, a straightforward solution is to convert your timestamps from milliseconds since epoch to PostgreSQL's timestamp type. This will make it easier for us to execute date manipulations without running into type errors. Below is a step-by-step breakdown of how to craft a solution.

Step-by-Step Breakdown

Setting Up Your Query:
Start by selecting values from your JSONB array housed within your specified table. Assuming your table is named mytable and the JSONB column is js, your base query would look like this:

[[See Video to Reveal this Text or Code Snippet]]

Extracting and Converting Values:
For each object in the JSONB array, extract the effectiveFrom and effectiveTo values, and convert them into timestamp types using the to_timestamp function. The conversion is done by dividing the bigint values by 1000 to turn milliseconds into seconds:

[[See Video to Reveal this Text or Code Snippet]]

Implementing the Conditions:
Now that we have our date values in the correct format, we can apply our conditions. Check if the effective_from timestamp is greater than the current time minus 7 days, or if effective_to is greater than effective_from plus one year:

[[See Video to Reveal this Text or Code Snippet]]

Full Query Example

By putting it all together, your complete SQL query will look something like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By utilizing conversions to timestamp, we can sidestep issues related to data types and perform robust date arithmetic in PostgreSQL with JSONB data. These solutions can help optimize your queries and cater to various timestamp-related applications efficiently.

If you ever find yourself grappling with date manipulations within your JSONB arrays, remember to convert your values to manageable timestamp formats. Happy querying!