CODEWITHSUNDEEP
javascriptnode.jspostgresql
wnba_youngboy
wnba_youngboy

Reputation: 69

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

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.

Upvotes: 1

Views: 422

Answers (1)

Blue Star
Blue Star

Reputation: 1952

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

Upvotes: 1

Related Questions