From Jed_Walker@cable.comcast.com Tue Aug  2 15:49:47 2005
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 629E1529BB
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 12:49:47 -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 35806-02
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 15:49:41 +0000 (GMT)
Received: from cable.comcast.com (paoakoavas09.cable.comcast.com
	[208.17.35.58])
	by svr1.postgresql.org (Postfix) with ESMTP id 81646529BF
	for <pgsql-novice@postgresql.org>; Tue,  2 Aug 2005 12:49:39 -0300 (ADT)
Received: from ([10.52.116.30])
	by paoakoavas09.cable.comcast.com with ESMTP  id KP-TDCH7.12632528;
	Tue, 02 Aug 2005 11:49:20 -0400
Received: from COENGEXCMB01.cable.comcast.com ([10.168.220.1]) by
	PAOAKEXCSMTP01.cable.comcast.com with Microsoft
	SMTPSVC(6.0.3790.211); Tue, 2 Aug 2005 11:49:20 -0400
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C59779.BE782C13"
Subject: Make year 01/01/0001 but leave timestamp alone
Date: Tue, 2 Aug 2005 09:49:19 -0600
Message-ID: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Make year 01/01/0001 but leave timestamp alone
Thread-Index: AcWXeb5x9ZUvYHiCT6OPuIFsMcs1aQ==
From: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
To: <pgsql-novice@postgresql.org>
X-OriginalArrivalTime: 02 Aug 2005 15:49:20.0433 (UTC)
	FILETIME=[BF2F9210:01C59779]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, HTML_50_60,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200508/8
X-Sequence-Number: 14029

This is a multi-part message in MIME format.

------_=_NextPart_001_01C59779.BE782C13
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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?

Thanks,

Jed



------_=_NextPart_001_01C59779.BE782C13
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7226.0">
<TITLE>Make year 01/01/0001 but leave timestamp alone</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->

<P><FONT SIZE=3D2 FACE=3D"Arial">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?</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Thanks,</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Jed</FONT>
</P>
<BR>

</BODY>
</HTML>
------_=_NextPart_001_01C59779.BE782C13--



From keithw@narrowpathinc.com Tue Aug  2 16:15:13 2005
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 722BF529D2
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 13:15:13 -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 40332-10
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 16:15:02 +0000 (GMT)
Received: from mail.99main.com (mail.99main.com [208.28.184.11])
	by svr1.postgresql.org (Postfix) with ESMTP id 9EBCE528EF
	for <pgsql-novice@postgresql.org>; Tue,  2 Aug 2005 13:14:59 -0300 (ADT)
Received: from 99main.com (localhost [127.0.0.1])
	by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j72GEuHm002151;
	Tue, 2 Aug 2005 12:14:58 -0400 (EDT)
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
Cc: <pgsql-novice@postgresql.org>
Reply-To: KeithW@narrowpathinc.com
Subject: Re: Make year 01/01/0001 but leave timestamp alone
Date: Tue, 2 Aug 2005 12:14:56 -0400
Message-Id: <20050802161204.M17641@narrowpathinc.com>
In-Reply-To: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
References: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
X-Mailer: Open WebMail 2.50 20050106
X-OriginatingIP: 68.235.22.242 (npkeithw)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200508/9
X-Sequence-Number: 14030

On Tue, 2 Aug 2005 09:49:19 -0600, 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?
> 
> Thanks,
> 
> Jed

Jed,

There may be a more elegant way to do it but this is the first thing that lept
to my mind.

SELECT '01/01/0001'::date,
       now()::time,
       (    '01/01/0001'::date::varchar
         || ' '
         || now()::time::varchar
       )::timestamp;

Kind Regards,
Keith



From daq@ugyvitelszolgaltato.hu Tue Aug  2 16:22:41 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5138E52E4E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 13:22:41 -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 43453-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 16:22:32 +0000 (GMT)
Received: from wukifi.ugyvitelszolgaltato.hu (adsl215141.vnet.hu
	[62.77.215.141])
	by svr1.postgresql.org (Postfix) with SMTP id A349F52E62
	for <Pgsql-sql@postgresql.org>; Tue,  2 Aug 2005 13:22:30 -0300 (ADT)
Received: (qmail 15719 invoked from network); 2 Aug 2005 15:30:00 -0000
Received: from unknown (HELO x-eef90d131bdb4) (193.80.82.4)
	by 193.80.82.16 with SMTP; 2 Aug 2005 15:30:00 -0000
Date: Tue, 2 Aug 2005 18:22:27 +0200
From: daq <daq@ugyvitelszolgaltato.hu>
X-Mailer: The Bat! (v1.53d) Business
Reply-To: daq16 <daq@ugyvitelszolgaltato.hu>
X-Priority: 3 (Normal)
Message-ID: <16139676609.20050802182227@ugyvitelszolgaltato.hu>
To: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
Cc: Pgsql-sql@postgresql.org
Subject: Re: [NOVICE] Make year 01/01/0001 but leave timestamp alone
In-Reply-To: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
References: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.935 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, RCVD_BY_IP, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL
X-Spam-Level: *
X-Archive-Number: 200508/10
X-Sequence-Number: 22238


WJS> I have a situation where we have a timestamp column but in special cases
WJS> we want to set the date to '01/01/0001' and leave the timestamp alone.
WJS> For example, '11/04/2005 10:30:05' would become '01/01/0001 10:30:05'.
WJS> I've been going through the various date time functions but don't see a
WJS> simple way to do this. Can someone help?

WJS> Thanks,

WJS> Jed

Maybe... Chomp the date part and concatenate the timestamp with
'01/01/0001'

DAQ




From desoi@pgedit.com Tue Aug  2 16:36:14 2005
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 F362F52C9B
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 13:36:14 -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 43380-10
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 16:36:10 +0000 (GMT)
Received: from a2s4.a2hosting.com (a2s4.a2hosting.com [64.9.205.220])
	by svr1.postgresql.org (Postfix) with ESMTP id 0F060529D2
	for <pgsql-novice@postgresql.org>; Tue,  2 Aug 2005 13:36:09 -0300 (ADT)
Received: from user-112057n.dsl.mindspring.com ([66.32.20.247]
	helo=[192.168.1.10])
	by a2s4.a2hosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.51)
	id 1DzzkN-0001hG-Vw; Tue, 02 Aug 2005 12:36:08 -0400
In-Reply-To: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
References: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
Mime-Version: 1.0 (Apple Message framework v733)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <C4C31703-544A-45D1-999A-46C7A4BF4B9D@pgedit.com>
Cc: <pgsql-novice@postgresql.org>
Content-Transfer-Encoding: 7bit
From: John DeSoi <desoi@pgedit.com>
Subject: Re: Make year 01/01/0001 but leave timestamp alone
Date: Tue, 2 Aug 2005 12:35:56 -0400
To: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
X-Mailer: Apple Mail (2.733)
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - a2s4.a2hosting.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - pgedit.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200508/10
X-Sequence-Number: 14031


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;

          timestamp
----------------------------
0001-01-01 12:31:52.815674
(1 row)


Just replace now() with whatever timestamp you are using.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




From tgl@sss.pgh.pa.us Tue Aug  2 20:57:12 2005
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 <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-novice@postgresql.org>; 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 <desoi@pgedit.com>
Cc: "Walker, Jed S" <Jed_Walker@cable.comcast.com>,
 pgsql-novice@postgresql.org
Subject: Re: Make year 01/01/0001 but leave timestamp alone 
In-reply-to: <C4C31703-544A-45D1-999A-46C7A4BF4B9D@pgedit.com> 
References: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
	<C4C31703-544A-45D1-999A-46C7A4BF4B9D@pgedit.com>
Comments: In-reply-to John DeSoi <desoi@pgedit.com>
	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 <tgl@sss.pgh.pa.us>
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 <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