Thread: Make year 01/01/0001 but leave timestamp alone
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
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
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
That's pretty clean and does the job. Thanks!
-----Original Message-----
From: John DeSoi [mailto:desoi@pgedit.com]
Sent: Tuesday, August 02, 2005 10:36 AM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Make year 01/01/0001 but leave timestamp alone
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
i have a situation where widgets either pass or fail a test and i record the result. i'm thinking of using boolean to track this - true for pass, false for fail. i've used int2 in other situations b/c i couldn't be as sure that only two outcomes were possible. i could use int2 here, also (ie, 0 for fail, 1 for pass). does it make a practical difference which one i use? as always, thanks for the help. it is much appreciated. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
i received the following...
SQL error:
ERROR: cannot extend t_inspection_inspection_id_seq:
No space left on device.
Check free disk space.
In statement:
CREATE TABLE "t_inspection" ("inspection_id" SERIAL
NOT NULL, "inspection_timestamp" timestamp without
time zone NOT NULL, "inspector_id" integer NOT NULL,
"serial_number_id" integer NOT NULL, "inspect_area_id"
integer NOT NULL, "inspect_result" boolean NOT NULL,
"inspection_notes" character varying,
"product_revision" character varying(3) NOT NULL)
WITHOUT OIDS
...after i tried to create a table.
does anyone have any ideas how to proceed?
tia...
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
operationsengineer1@yahoo.com wrote:
Jeff
Delete some stuff; new hard drive.i received the following... SQL error: ERROR: cannot extend t_inspection_inspection_id_seq: No space left on device. Check free disk space.
Jeff
On Tue, 2 Aug 2005 10:31:17 -0700 (PDT), operationsengineer1 wrote > i have a situation where widgets either pass or fail a > test and i record the result. > > i'm thinking of using boolean to track this - true for > pass, false for fail. > > i've used int2 in other situations b/c i couldn't be > as sure that only two outcomes were possible. i could > use int2 here, also (ie, 0 for fail, 1 for pass). > > does it make a practical difference which one i use? > > as always, thanks for the help. it is much appreciated. Personally I like to use a boolean to make the code a little more self documenting. With a column name like passed_test and a boolean your code ends up looking like WHERE passed_test or WHERE NOT passed_test Kind Regards, Keith
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
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
> Personally I like to use a boolean to make the code > a little more self > documenting. With a column name like passed_test > and a boolean your code ends > up looking like > > WHERE passed_test > > or > > WHERE NOT passed_test > > Kind Regards, > Keith i'm now applying your method - thanks. __________________________________ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html