Re: Selecting timestamp from Database

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> On 04/08/2013 06:49 AM, Richard Harley wrote:
>> It's
>> timestamp | timestamp without time zone | default now()

> Well timestamp is not time zone aware, so I have no idea where your time
> zone offsets are coming from.

I'm suspicious that they're being attached by some client-side software;
the server itself certainly would not show any time zone in the output
from a plain-timestamp column.

The other thing that's suspicious is that "default now()" would
typically inject a timestamp with a fractional-second part, but the
output we just looked at isn't showing any such thing. When I try
this here, I get:

regression=# create table tt(timestamp timestamp);
CREATE TABLE
regression=# insert into tt values(now());
INSERT 0 1
regression=# select * from tt;
timestamp
----------------------------
2013-04-08 10:05:34.202665
(1 row)

regression=# select * from tt where timestamp = '2013-04-08 10:05:34.202665';
timestamp
----------------------------
2013-04-08 10:05:34.202665
(1 row)

regression=# select * from tt where timestamp = '2013-04-08 10:05:34';
timestamp
-----------
(0 rows)

So the theory I'm wondering about is that the stored data in fact
contains (some values with) fractional seconds, but Richard's
client-side software isn't bothering to show those, misleading him
into entering values that don't actually match the stored data.
Looking at the table directly with psql would prove it one way
or the other.

A possible workaround if that's the case is to change the column
to be timestamp(0).

regards, tom lane