On 05/19/2012 10:34 AM, David Salisbury wrote:
CCing the list.
>
>
> On 5/19/12 8:12 AM, Adrian Klaver wrote:
>
> I hope no one looks further into the problem as the case is closed. It
> was a coding
> problem and not a time matchup problem. Late Friday afternoons just
> aren't my most
> shining moments. ;)
>
> But I do still wonder... Is there a setting that I can set a default
> formatting of the date. If I do a "select timestamp '01-jan-2012'" I
> would want
> it to always return everything, down to the last microsecond. A "Give me
> everything!"
> setting without doing some to_char ugliness. It would just return..
>
> 01-jan-2012 00:00:00.000
<NOTE: A timestamp contains a date, but a date is not a
timestamp(actually it is one fixed at midnight). To avoid confusion you
might want to be specific what type of time you are working with.>
It will return what is provided:) In other words it depends on how the
timestamp field is set up. That is determined by the precision parameter
passed to the timezone type when creating or altering the field. As far
as ad-hoc timestamp as shown above the best you can do is change the
DateStyle but that only goes to two decimal places.:
test=# SET datestyle ='SQL';
SET
test=# SELECT timestamp '01-jan-2012';
timestamp
---------------------
01/01/2012 00:00:00
(1 row)
In either case it will return all significant digits:
test=# SELECT '01-jan-2012 00:00:00.000012'::timestamp;
timestamp
----------------------------
2012-01-01 00:00:00.000012
>
>
> And conversely, is there a setting so that any time value will always be
> rounded to the second,
> shaving off the micro-seconds. So that select now(); would return, and
> store _internally_! ..
>
> 2012-05-19 11:25:12.000
Change the precision of the timestamp field to 0. See below:
test=# \d timestamp_test
Table "public.timestamp_test"
Column | Type | Modifiers
---------+-----------------------------+-----------
id | integer | not null
txt_fld | text |
ts_fld | timestamp with time zone |
ts_fld2 | timestamp(0) with time zone |
Indexes:
"timestamp_test_pkey" PRIMARY KEY, btree (id)
test=# SELECT * from timestamp_test ;
id | txt_fld | ts_fld | ts_fld2
----+---------+-------------------------------+------------------------
7 | test4 | 2009-12-24 13:37:32.499764-08 | 2009-12-24 13:37:32-08
8 | t | 2010-05-20 08:13:28.157027-07 | 2010-05-20 12:13:28-07
9 | t | 2010-05-20 08:13:43.265383-07 | 2010-05-20 10:13:43-07
10 | t | 2010-05-20 08:13:53.718519-07 | 2010-05-20 13:13:54-07
11 | s | 2011-03-25 09:00:00.124-07 | 2011-03-25 14:15:13-07
12 | s | 2011-03-25 09:12:00.124-07 | 2011-03-25 14:16:27-07
>
>
> Thanks for the reply and any help!
>
> -ds
--
Adrian Klaver
adrian.klaver@gmail.com
