postgresql: time stored as text. how to query with respect to time

Question

I have a table called device with following data types

device table:

column        Type
id            integer
created       text
name          text

Here the time is stored in text type instead of timestamp

Eg: created value 12/19/2020 20:40:23

I try to query with this date time.

SELECT "device"."id",
       "device"."created",
       "device"."name",
FROM "device"
WHERE "device"."created" < '12/19/2020 20:40:23'
LIMIT 21

the results are not as per datetime order. it might be comparing some text string.

So what is the best solution in this case to get data w.r.t time eventhough its stored as text

Answer 1

You need to convert the text to timestamp. In this case a simple convertion suffices.

SELECT device.id,
       device.created,
       device.name,
FROM device
WHERE device.created::timestamp < '12/19/2020 20:40:23'
LIMIT 21