A PostgreSQL timestamp field not formated in the same way when updated with PHP

Question

I have a timestamp with timezone field in PostgreSQL. When I update this field, I use something like this:

$date = date('Y-m-d H:i:s');

Although the SQL works fine, the date saved seem a little bit different to a classic timestamp with timezone date.

Example:

Default value set to "now()":
date 2009-04-06 14:39:53.662522+02

Update with a date set in php:
$date = date('Y-m-d H:i:s');
date 2009-04-06 14:39:53+02

The numbers removed on update are probably milliseconds but I'm not sure. I would like to know if there is a means to obtain with PHP the same format of date?

Tometzky · Accepted Answer · 2009-04-07 08:13:20Z

1

If you only need one second resolution of timestamp you have to design your database accordingly, as by default resolution is better than a second.

Use for example the following column definition:

last_access_time timestamp with time zone not null
    default date_trunc('second',now())
    constraint last_access_time_full_second check (
        date_trunc('second',last_access_time)=last_access_time
    )

answered Apr 7, 2009 at 8:13

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

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

1 Comment

Milen A. Radev Over a year ago

Fortunatelly the column definition could be much simpler (at least the part for the default value): last_access_time timestamp(0) with time zone default CURRENT_TIMESTAMP

Community · Accepted Answer · 2023-11-20 22:45:59Z

0

You can use the microtime() PHP function to get the microseconds.

edited Nov 20, 2023 at 22:45

CommunityBot

11 silver badge

answered Apr 6, 2009 at 20:48

user39113

2972 silver badges4 bronze badges

Collectives™ on Stack Overflow

A PostgreSQL timestamp field not formated in the same way when updated with PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related