Question 263984 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query Postgres JSONB column timestamp property between two timestamps using Spring Data JPA

I have a jsonb column which has ‘created’ property and stores timestamp string. I’m able to query in query editor, but I can’t perform the same using Spring Data JPA with nativeQuery as true. Here is what I’m trying to do

In pgAdmin/Query Editor I get results:

select id, text_code, json_data from some_table where text_code = '7845754645434878' and ((json_data ->> 'created')::timestamp) 
between '2023-10-10:00:00:00' and '2023-10-10:23:59:59'

Using code:

@Query(
        nativeQuery = true,
        value =
                """
                SELECT id, text_code as textCode, json_data as jsonData
                FROM some_table
                WHERE text_code = :textCode
                    AND ((json_data ->> 'created')::timestamp) BETWEEN :startDate AND :endDate
               """)
List<EntityData> getBetween(
        @Param("textCode") String textCode,
        @Param("startDate") String startDate,
        @Param("endDate") String endDate);

I get syntax error

ERROR: syntax error at or near ":"


Position: 198] [n/a]; SQL [n/a]

I think it is showing error at :timestamp, but I’m not able to resolve this. Can you please help to get a workaround for this?

Update:

The data I sent to startDate and endDate is ‘2023-10-10:00:00:00’ and ‘2023-10-10:23:59:59’ respectively.

If I do casting, I get

m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ SELECT id, text_code as textCode, json_data as jsonData<EOL> FROM some_table<EOL> WHERE text_code = ?<EOL>     AND ((data_stream ->> 'created')::timestamp) BETWEEN ? AND ?<EOL>] [ERROR: operator does not exist: timestamp without time zone >= character varying<EOL>  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.<EOL>  Position: 211] [n/a]; SQL [n/a]]
Tags:

2

Answers


  1. 0

    You have to escape the colons in ::timestamp because jpa will looking for a placeholder if it sees a colon:

    AND ((json_data ->> 'created')\:\:timestamp)

    or if you use text blocks

    AND ((json_data ->> 'created')::timestamp)
    Login or Signup to reply.
  2. 0

    Use CAST() to cast:

    Query = true,
        value =
                """
                SELECT id, text_code as textCode, json_data as jsonData
                FROM some_table
                WHERE text_code = :textCode
                    AND CAST(json_data ->> 'created' AS timestamp) BETWEEN CAST(:startDate AS timestamp) AND CAST(:endDate AS timestamp);""")
List<EntityData> getBetween(
        @Param("textCode") String textCode,
        @Param("startDate") String startDate,
        @Param("endDate") String endDate);

    Simple example that works perfectly fine:

    SELECT  CAST(x->>'ts' AS TIMESTAMP)
FROM ( SELECT  json_build_object('ts','2023-10-12 12:00')) AS t1(x);

    However, an empty string or any other non-valid timestamp will result in an error.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search