pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMake year 01/01/0001 but leave timestamp alone
5+ messages / 5 participants
[nested] [flat]
* Make year 01/01/0001 but leave timestamp alone
@ 2005-08-02 15:49 Walker, Jed S <Jed_Walker@cable.comcast.com>
0 siblings, 3 replies; 5+ messages in thread
From: Walker, Jed S @ 2005-08-02 15:49 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Make year 01/01/0001 but leave timestamp alone
@ 2005-08-02 16:14 Keith Worthington <keithw@narrowpathinc.com>
parent: Walker, Jed S <Jed_Walker@cable.comcast.com>
2 siblings, 0 replies; 5+ messages in thread
From: Keith Worthington @ 2005-08-02 16:14 UTC (permalink / raw)
To: Walker, Jed S <Jed_Walker@cable.comcast.com>; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [NOVICE] Make year 01/01/0001 but leave timestamp alone
@ 2005-08-02 16:22 daq <daq@ugyvitelszolgaltato.hu>
parent: Walker, Jed S <Jed_Walker@cable.comcast.com>
2 siblings, 0 replies; 5+ messages in thread
From: daq @ 2005-08-02 16:22 UTC (permalink / raw)
To: Walker, Jed S <Jed_Walker@cable.comcast.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Make year 01/01/0001 but leave timestamp alone
@ 2005-08-02 16:35 John DeSoi <desoi@pgedit.com>
parent: Walker, Jed S <Jed_Walker@cable.comcast.com>
2 siblings, 1 reply; 5+ messages in thread
From: John DeSoi @ 2005-08-02 16:35 UTC (permalink / raw)
To: Walker, Jed S <Jed_Walker@cable.comcast.com>; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Make year 01/01/0001 but leave timestamp alone
@ 2005-08-02 20:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-08-02 20:57 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: Walker, Jed S <Jed_Walker@cable.comcast.com>; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-08-02 20:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-02 15:49 Make year 01/01/0001 but leave timestamp alone Walker, Jed S <Jed_Walker@cable.comcast.com>
2005-08-02 16:14 ` Keith Worthington <keithw@narrowpathinc.com>
2005-08-02 16:22 ` daq <daq@ugyvitelszolgaltato.hu>
2005-08-02 16:35 ` John DeSoi <desoi@pgedit.com>
2005-08-02 20:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox