Re: Make year 01/01/0001 but leave timestamp alone

That works too, but here's what I'm worried about now. Am I losing the
time zone (the original shows timezone -6 but the inserted version shows
-7)

VRNJED=# select '01/01/0001'::timestamp +  start_time::time , start_time
from page_schedule limit 5;
      ?column?       |       start_time
---------------------+------------------------
 0001-01-01 15:20:55 | 2005-06-20 15:20:55-06
 0001-01-01 17:01:00 | 2005-06-20 17:01:00-06
 0001-01-01 18:40:55 | 2005-06-20 18:40:55-06
 0001-01-01 15:30:00 | 2005-06-20 15:30:00-06
 0001-01-01 15:32:30 | 2005-06-20 15:32:30-06
(5 rows)

VRNJED=# \d jed
                Table "vrnsys.jed"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 col1   | timestamp(0) with time zone |

VRNJED=# insert into jed select '01/01/0001'::timestamp +
start_time::time  from page_schedule limit 5; INSERT 0 5 VRNJED=# select
* from jed;
          col1
------------------------
 0001-01-01 15:20:55-07
 0001-01-01 17:01:00-07
 0001-01-01 18:40:55-07
 0001-01-01 15:30:00-07
 0001-01-01 15:32:30-07
(5 rows)

VRNJED=#

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 02, 2005 2:57 PM
To: John DeSoi
Cc: Walker, Jed S; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Make year 01/01/0001 but leave timestamp alone

John DeSoi <desoi@pgedit.com> writes:
> On Aug 2, 2005, at 11:49 AM, Walker, Jed S wrote:
>> I have a situation where we have a timestamp column but in special
>> cases we want to set the date to '01/01/0001' and leave the timestamp

>> alone. For example, '11/04/2005 10:30:05' would become
>> '01/01/0001 10:30:05'. I've been going through the various date time
>> functions but don't see a simple way to do this. Can someone help?

> select ('0001-01-01 ' || now()::time::text)::timestamp;

This textual pasting makes me itch ... try something like

select '0001-01-01'::timestamp + now()::time;
          ?column?
----------------------------
 0001-01-01 16:56:16.313866
(1 row)


            regards, tom lane