How to query in order of custom date format in postgres?

Question

I have a postgres table with a column "date" which is a string with the basic javascript date format:

"Tue Dec 29 2020 23:16:54 GMT-0800 (Pacific Standard Time)"

I am wondering if there is a way to query by date even though it's in a string format like that. In my node backend, I am currently doing this:

  pool.query("SELECT * FROM table WHERE field1 = $1 ORDER BY date DESC LIMIT 10", [val1], (err, table) => {
      if(err) {
        return res.status(400)
        done()
      } else {
        res.json(table.rows)
      }
    })

This appeared to be working at first but since adding more records it is not really working correctly. If there is a fix for this that doesn't involve redoing the date column, I would be infinitely grateful as I've already built out a lot of code around that date string format. Thank you in advance.

You could order the data in JavaScript by converting to a Date, but in the long run changing the date column to DATETIME or TIMESTAMP is the correct solution. — Nick
– Nick, Commented Jan 3, 2021 at 0:44
First you are working with a datetime/timestamp format not a date format. Second if you want to work with a datetime/timestamp use native types not a string. Formatting is the final step in the process and is for end user comfort not querying. — Adrian Klaver
– Adrian Klaver, Commented Jan 3, 2021 at 0:50

Blue Star · Accepted Answer · 2021-01-03 01:18:09Z

1

As the commenters have noted, you would be much better served by a timestamp column than a string column. However, if you can't change your schema, then you can convert your timestamp string to an actual timestamp before ordering by it, like this -

SELECT * FROM table 
WHERE field1 = $1 
ORDER BY to_timestamp(date, 'Dy Mon DD YYYY HH24:MI:SS') DESC 
LIMIT 10

answered Jan 3, 2021 at 1:18

Blue Star

1,9621 gold badge13 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

How to query in order of custom date format in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related