X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 76C4D528CC for ; Tue, 2 Aug 2005 17:57:12 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07346-04 for ; Tue, 2 Aug 2005 20:57:10 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id C1224528B7 for ; Tue, 2 Aug 2005 17:57:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j72Kv9hH024441; Tue, 2 Aug 2005 16:57:09 -0400 (EDT) To: John DeSoi Cc: "Walker, Jed S" , pgsql-novice@postgresql.org Subject: Re: Make year 01/01/0001 but leave timestamp alone In-reply-to: References: <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com> Comments: In-reply-to John DeSoi message dated "Tue, 02 Aug 2005 12:35:56 -0400" Date: Tue, 02 Aug 2005 16:57:09 -0400 Message-ID: <24440.1123016229@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/16 X-Sequence-Number: 14037 John DeSoi 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