Home Postgresql returned timestamp format differes from test and prod db?
Reply: 0

Postgresql returned timestamp format differes from test and prod db?

user1729
1#
user1729 Published in April 25, 2018, 5:09 am

I have a column called start_date in table X.

In my php project, when I use a simple select * from table x where id = 1 to retrieve a row from table X in my test DB, the following is returned:

{"id":1,"start_date":"2017-12-05 18:56:07"}

Now when I do the same in my production database the following is returned:

{"id":1,"start_date":"05\/12\/2017 18:56:07"

Both my databases have the same datestyle which I have set to ISO,DMY using

ALTER DATABASE xx SET datestyle TO ISO, DMY;

Why if both of these settings are the same and my production db is a restore of my test db, are these two queries returning different formats?

Edit: I'm using codeigniters query builder in my php project to retrieve the values from the db. I then view the value of the direct result using var_dump. Here is my php codeigniter method:

  function get_last($id) {
    $this->db->where ( "id", $id);      
    return $this->db->get ('table_x')->row_array ();
  }

If I replace the function with the following it works, but I would still like to know why the top is returning two different results.

  function get_last($id) {
    $this->db->select("id, to_char(dataman, 'DD/MM/YYYY') as start_date");
    $this->db->where ( "id", $id);      
    return $this->db->get ('table_x')->row_array ();
  }

This also makes me believe even more that postgregsql is the reason the format is coming back different since by directly defining the format works.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305317 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO