PostgreSQL: insert sql statement with default timestamp - dbeaver vs php

Question

Given this simple table:

create table comments (
  id numeric not null,
  comment text, 
  created timestamp not null default now()
);

Using dbeaver directly on the db from my pc, and executing this statement

insert into comments (id, comment)
select 1111, 'test';

the "created" field of the new record is: 2017-02-24 16:17:21 and that's my correct time (CET).

When I run the very same statement from a php script (running on a linux-based webserver connected to the db), the result is 2017-02-24 15:17:21. The linux server time is ok (that is, CET).

What am I missing?

In your PHP ini file, check the selected timezone. EDIT: Actually not quite sure why this would matter — Andy, Feb 24 '17 at 15:39
@Andy I already tried to set date_default_timezone_set('CET'); — Giovanni Formentini, Feb 24 '17 at 16:03
Have you tried this? http://stackoverflow.com/questions/16609724/using-current-time-in-utc-as-default-value-in-postgresql — Andy, Feb 24 '17 at 19:15
@Andy default (now() at time zone ('CET')) worked like a charm. Thank you! — Giovanni Formentini, Feb 27 '17 at 08:17

score 0 · Answer 1 · edited May 23 '17 at 12:25

0

As per the answer at this link, change your table definition to

create table comments (
   id numeric not null,
   comment text, 
   created timestamp not null default (now() at time zone ('CET'))
);

which will use the current datetime using the desired time zone.

edited May 23 '17 at 12:25

Community

1
1

answered Feb 27 '17 at 14:18

Andy

698
12
22

PostgreSQL: insert sql statement with default timestamp - dbeaver vs php

1 Answers1