alter date - postgreSQL and websockets

Question

alter date - postgreSQL and websockets

I am using websockets , nodejs v0.10.12 and also PostgreSQL 9.1, with PostGIS 2.0. Now, on websockets, on the server side, in order to gather textual data and send them to the client I perform a query using node's pg plugin. I have something like

var query = client.query('SELECT p_name,p_date FROM  pins WHERE p_id ='+ja)
//send them and render in client as html
query.on("row", function (row, result) {result.addRow(row);});

query.on("end", function (result) {
    for (var i=0; i<result.rows.length; i++){
           connection.send(
                        'Name</br>'
                        +result.rows[i].p_name+
                        '</br>Date</br>'
                        +result.rows[i].p_date+
                        '</br>'
                        }

      client.end();
 });

Now, here is the tricky part. I want to render the date like 25/02/2012. With the above code, I get Sat Feb 02 2002 02:00:00 GMT+0200 (Χειμερινή ώρα GTB) To get DD/MM/YYYY I have to put a line of code like

SET datestyle = "SQL, DMY";

This is apparently PHP and I am using Javascript because I work with websockets.

The only thing I could think of is editing the above query like so

var query = client.query('SET datestyle = "SQL, DMY"; SELECT p_name,p_date FROM  pins WHERE p_id ='+ja)

I dont get any errors, but on the client the date renders null.

How can I fix this?

Thanks

node.js
postgresql
websocket

Answer 1

OK. Where to start?

This:

var query = client.query('SELECT p_name,p_date FROM  pins WHERE p_id ='+ja)

is not the correct way to build a query. Used a parameterised query and protect yourself from SQL injection.

SET datestyle = "SQL, DMY";

This is apparently PHP and I am using Javascript because I work with websockets.

What? I'm trying to think of something constructive about this sentence, but the best I can think of is "What?". It is far from apparent that the above is PHP, because it isn't. The fact that you are sending it to the database ought to give you a hint that it's SQL. Also, you're not using javascript because you work with websockets. You're using javascript because you're using javascript - websockets are nothing to do with anything here.

The only thing I could think of...

Doesn't include looking in the manuals.

Go to the PostgreSQL website, click through to the documentation and manuals, and on the contents page click "Functions and Operators" and then "Data type formatting functions". Here is the link for you:

http://www.postgresql.org/docs/current/static/functions-formatting.html

You'll notice that the PostgreSQL developers not only produce extensive and detailed manuals, but they keep multiple versions online and make it simple to switch back and fore to see what's changed.

There is a whole section on this page on how to format date-times in different ways, with clear descriptions of each effect. I didn't find this using the documentation search or anything clever like that - just the obvious links on each page.

If you did a search you would find plenty on the datestyle parameter, and a little further digging would show that you can set it per-session or as a default for a given user or database.

Finally though, don't do it that way at all. Return ISO-standard date formats like @mu said (YYYY-MM-DD etc). and format them in your javascript client code.

Oh - while I'm no expert, I'm not sure that </br> is valid HTML, XHTML or XML either. Did you perhaps mean <br/>?