Selecting timestamp from Database

On 04/08/2013 06:03 AM, Richard Harley wrote:

Hello all

Pretty sure this should be simple - how can I select a timestamp from a
database?

The timestamp is stored in the db like this:

2013/04/08 13:54:41 GMT+1

How can I select based on that timestamp?

At the simplest level "select timestamp from attendance where timestamp
= '2013/04/08 13:54:41 GMT+1'"

..doesn't obviously work but I've tried all sorts of to_char and
to_timestamp combos to no avail..

Any ideas?

select timestamp from attendance where timestamp = '2013/04/08 13:54:41+1'

Cheers
Rich

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This doesn't seem to work - take a normal GMT date for example: 2012/12/14 12:02:45 GMT

select timestamp from attendance where timestamp = '2012/12/14 12:02:45'

..returns nothing

On 8 Apr 2013, at 14:17, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 04/08/2013 06:22 AM, Richard Harley wrote:

This doesn't seem to work - take a normal GMT date for example:
2012/12/14 12:02:45 GMT

select timestamp from attendance where timestamp = '2012/12/14 12:02:45'

..returns nothing

Can you show the results of an unconstrained SELECT?:

select timestamp from attendance limit 5;

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sure

Timestamp
2013/04/08 12:42:40 GMT+1
2013/04/08 12:42:33 GMT+1
2013/04/07 20:25:11 GMT+1
2013/04/07 20:19:52 GMT+1
2013/04/07 20:19:52 GMT+1

Some are GMT, some are GMT+1 depending on when they were entered.

On 8 Apr 2013, at 14:25, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 04/08/2013 06:27 AM, Richard Harley wrote:

Sure

Timestamp
2013/04/08 12:42:40 GMT+1
2013/04/08 12:42:33 GMT+1
2013/04/07 20:25:11 GMT+1
2013/04/07 20:19:52 GMT+1
2013/04/07 20:19:52 GMT+1

What program are you using to get the above result?

What is the field definition for the timestamp column?

From your previous post try:

select timestamp from attendance where timestamp = '2012/12/14 12:02:45+0'

Some are GMT, some are GMT+1 depending on when they were entered.

On 8 Apr 2013, at 14:25, Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>> wrote:

On 04/08/2013 06:22 AM, Richard Harley wrote:

This doesn't seem to work - take a normal GMT date for example:
2012/12/14 12:02:45 GMT

select timestamp from attendance where timestamp = '2012/12/14 12:02:45'

..returns nothing

Can you show the results of an unconstrained SELECT?:

select timestamp from attendance limit 5;

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I am running the query straight through PSQL so there are no other programs or adapters.

The field definition is just 'timestamp'.

I did try that as well - no luck :)

Rich

On 8 Apr 2013, at 14:36, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 04/08/2013 06:27 AM, Richard Harley wrote:

Sure

Timestamp
2013/04/08 12:42:40 GMT+1
2013/04/08 12:42:33 GMT+1
2013/04/07 20:25:11 GMT+1
2013/04/07 20:19:52 GMT+1
2013/04/07 20:19:52 GMT+1

What program are you using to get the above result?

What is the field definition for the timestamp column?

From your previous post try:

select timestamp from attendance where timestamp = '2012/12/14 12:02:45+0'

Some are GMT, some are GMT+1 depending on when they were entered.

On 8 Apr 2013, at 14:25, Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.com>> wrote:

On 04/08/2013 06:22 AM, Richard Harley wrote:

This doesn't seem to work - take a normal GMT date for example:
2012/12/14 12:02:45 GMT

select timestamp from attendance where timestamp = '2012/12/14 12:02:45'

..returns nothing

Can you show the results of an unconstrained SELECT?:

select timestamp from attendance limit 5;

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/08/2013 06:45 AM, Richard Harley wrote:

I am running the query straight through PSQL so there are no other programs or adapters.

The field definition is just 'timestamp'.

From psql what do you get if you do?:

\d attendance

I did try that as well - no luck :)

Rich

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

It's

Column | Type | Modifiers
--------------+-----------------------------+-------------------------------------------------------------------
attendanceid | integer | not null default nextval('attendance_attendanceid_seq'::regclass)
entered | date | not null default ('now'::text)::date
timeperiod | character(2) |
timestamp | timestamp without time zone | default now()

On 8 Apr 2013, at 14:48, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 04/08/2013 06:49 AM, Richard Harley wrote:

It's

Column | Type |
Modifiers
--------------+-----------------------------+-------------------------------------------------------------------
attendanceid | integer | not null default
nextval('attendance_attendanceid_seq'::regclass)
entered | date | not null default
('now'::text)::date
timeperiod | character(2) |
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.

What happens if you do:
"select timestamp from attendance where timestamp = ' '2012-12-14 12:02:45';

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

That returns nothings also. But I have spied the problem now:
select ATTENDANCE.timestamp::text from attendance order by timestamp desc limit 1
return the actual timestamp: 2013-04-08 12:42:40.089952

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.

This is it. It was the psycopg adapter. My bad!!
Thanks Adrian / Tom.
Rich

On 8 Apr 2013, at 14:58, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On Mon, Apr 8, 2013 at 3:15 PM, Richard Harley <richard@scholarpack.com> wrote:

That returns nothings also. But I have spied the problem now:

select ATTENDANCE.timestamp::text from attendance order by timestamp desc
limit 1

return the actual timestamp: 2013-04-08 12:42:40.089952

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.

This is it. It was the psycopg adapter. My bad!!

This message can be misread as psycopg dropping the fractional part of
the timestamp, which is not the case:

cur.execute("select '2013-04-08 12:42:40.089952'::timestamp")
cur.fetchone()[0]

datetime.datetime(2013, 4, 8, 12, 42, 40, 89952)

Just FYI.

-- Daniele

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 04/08/2013 08:28 AM, Daniele Varrazzo wrote:

On Mon, Apr 8, 2013 at 3:15 PM, Richard Harley <richard@scholarpack.com> wrote:

That returns nothings also. But I have spied the problem now:

select ATTENDANCE.timestamp::text from attendance order by timestamp desc
limit 1

return the actual timestamp: 2013-04-08 12:42:40.089952

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.

This is it. It was the psycopg adapter. My bad!!

This message can be misread as psycopg dropping the fractional part of
the timestamp, which is not the case:

cur.execute("select '2013-04-08 12:42:40.089952'::timestamp")
cur.fetchone()[0]

datetime.datetime(2013, 4, 8, 12, 42, 40, 89952)

Just FYI.

Well, pretty sure the returned results where getting massaged at some
point between pyscopg2 and the screen, only way I can figure timezone
information was turning up in non-timezone aware values.

-- Daniele

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general