1
I have a date field that is saved to the field in JSON format, along with other values
{"data_limite":"14\/07\/2016","data_programada":null,"admin_agendamento":"4813"}
This field "deadline", I need, in another screen, put in a CASE-WHEN to check if that date is the 24, 48 or 72 hours to arrive today.
This is my question: How to perform a comparison in a CASE-WHEN with a data saved with other values?
EDIT:My postgres is 8.3, so without JSON support, it is saved in a TEXT field
EDIT 2: Excerpt from SQL that I have to execute:
...
CASE
WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE >= 3
THEN 3
WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE > 1
THEN 2
WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE <= 1
THEN 1
ELSE 4
END AS termino_atendimento
...
Do you need to make this comparison directly in the database or is it in the application? (PHP, etc..)
– Lucas Queiroz Ribeiro
Yes, right in the bank, because I have no previous parameters to search before and do in the application. I need to take this "data_limite" and make our beloved INTERVAL 1, 2 or 3 days.
– William Aparecido Brandino
I found this link might help: http://stackoverflow.com/questions/30836025/postgresql-query-between-time-range-using-jsonb-field according to the link ->> extracts the field from within the Json
– Lucas Queiroz Ribeiro
It would help @Lucasqueirozribeiro, but as I said in my EDIT, my bank is 8.2, it doesn’t support JSON. This support is from 9. By my walks, I would need to do a sub-select to search inside each CASE, but it would be too heavy.
– William Aparecido Brandino
I found what I needed:
split_part()
. I did the tests and answered me. Thank you for your attention.– William Aparecido Brandino