Thread: 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

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

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

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

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

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

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

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

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