Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
4.4k views
in Technique[技术] by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...