text to timestamp in postgresql

Question

In the view I have a text column which contains a timestamp in this format '20/03/2018 00:00' and I'm trying to make a selection with a between clause but it's not working

SELECT id,entry_date
FROM v_view 
WHERE entrada BETWEEN to_timestamp('20/03/2018 00:00','DD/MM/YYYY')::timestamp and to_timestamp('22/03/2018 00:00')::timestamp
order entry_date

with this error message

ERROR:  el operador no existe: text >= timestamp without time zone
LINE 3: WHERE entry_date BETWEEN to_timestamp('20/03/2018 00:00','DD/MM.

If the entrada column contains date like 20/03/2018 14:20, You probably need ... WHERE to_timestamp(entrada, 'DD/MM/YYYY') BETWEEN .... — Roman Hocke
– Roman Hocke, Commented Mar 22, 2018 at 9:44

user330315 · Accepted Answer · 2018-03-22 09:48:45Z

2

you need to convert the entrada column value to a timestamp.

Also: casting the result of to_timestamp() to a timestamp is useless because to_timestamp() already returns a timestamp

SELECT id,entry_date
FROM v_view 
WHERE to_timestamp(entrada, 'dd/mm/yyyy hh24:mi') 
      BETWEEN to_timestamp('20/03/2018', 'DD/MM/YYYY') 
          and to_timestamp('22/03/2018', 'dd/mm/yyyy')
order entry_date;

I prefer to use ANSI SQL timestamp literals over the to_timestamp function:

SELECT id,entry_date
FROM v_view 
WHERE to_timestamp(entrada, 'dd/mm/yyyy hh24:mi') 
      BETWEEN timestamp '2018-03-20 00:00:00' 
          and timestamp '2018-03-22 00:00:00'
order entry_date

Do not store date, time or timestamp values in a text or varchar column. You should define that column as timestamp then you don't need to convert anything and you don't need to deal with invalid timestamp values in that column.

answered Mar 22, 2018 at 9:48

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Michel Milezzi Over a year ago

+1. Let me just warn that'll avoid index usage, so follow @a_horse_with_no_name advice and store timestamp as timestamps :)

Collectives™ on Stack Overflow

text to timestamp in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related