pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with timestamp - Pls help
6+ messages / 4 participants
[nested] [flat]
* Problem with timestamp - Pls help
@ 2003-09-19 13:14 Kumar <sgnerd@yahoo.com.sg>
2003-09-19 14:03 ` Re: Problem with timestamp - Pls help Tomasz Myrta <jasiek@klaster.net>
2003-09-19 15:39 ` Re: Problem with timestamp - Pls help Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 15:42 ` Re: Problem with timestamp - Pls help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 6+ messages in thread
From: Kumar @ 2003-09-19 13:14 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
I am using Postgres 7.3.4 on Linux server 7.3.
I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format ------------------------
2003-09-19 18:39:08.13
To achieve this I have used the following
wats=# select now();
now
------------------------------
2003-09-19 18:39:58.62398+00
(1 row)
wats=# select substr(now(),1,22);
substr
------------------------
2003-09-19 18:40:01.25
(1 row)
wats=# select timestamp(substr(now(),1,22));
ERROR: parser: parse error at or near "substr" at character 18
wats=# select to_timestamp(substr(now(),1,22));
ERROR: Function to_timestamp(text) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
wats=# select date(substr(now(),1,22));
date
------------
2003-09-19
(1 row)
Also I cant directly update my timestamp column as follows
update "WATS".users set to_rec_modified_date = substr(now(),1,22);
ERROR: column "rec_modified_date" is of type timestamp without time zone but expression is of type text
You will need to rewrite or cast the expression
Please help me with this.
Regards
Senthil Kumar S
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with timestamp - Pls help
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
@ 2003-09-19 14:03 ` Tomasz Myrta <jasiek@klaster.net>
2 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-09-19 14:03 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
> Dear Friends,
>
> I am using Postgres 7.3.4 on Linux server 7.3.
>
> I wanted to update one column of my table with now() or timestamp. And I
> want that timestamp of format ------------------------
> 2003-09-19 18:39:08.13
>
> To achieve this I have used the following
>
> wats=# select now();
> now
> ------------------------------
> 2003-09-19 18:39:58.62398+00
> (1 row)
>
> wats=# select substr(now(),1,22);
> substr
> ------------------------
> 2003-09-19 18:40:01.25
> (1 row)
>
> wats=# select timestamp(substr(now(),1,22));
> ERROR: parser: parse error at or near "substr" at character 18
select substr(now(),1,22)::timestamp
or
select cast(substr(now(),1,22) as timestamp)
> Also I cant directly update my timestamp column as follows
>
>
> update "WATS".users set to_rec_modified_date = substr(now(),1,22);
>
> ERROR: column "rec_modified_date" is of type timestamp without time
> zone but expression is of type text
> You will need to rewrite or cast the expression
Use syntax above.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with timestamp - Pls help
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
@ 2003-09-19 15:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 15:59 ` Re: Problem with timestamp - Pls help Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 16:27 ` Re: Problem with timestamp - Pls help Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-09-19 15:39 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
On Fri, 19 Sep 2003, Kumar wrote:
> Dear Friends,
>
> I am using Postgres 7.3.4 on Linux server 7.3.
>
> I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format ------------------------
> 2003-09-19 18:39:08.13
I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)
may get you what you want without having to rely on the text format.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with timestamp - Pls help
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
2003-09-19 15:39 ` Re: Problem with timestamp - Pls help Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-09-19 15:59 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2003-09-19 15:59 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
On Fri, 19 Sep 2003, Stephan Szabo wrote:
> On Fri, 19 Sep 2003, Kumar wrote:
>
> > Dear Friends,
> >
> > I am using Postgres 7.3.4 on Linux server 7.3.
> >
> > I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format ------------------------
>
> > 2003-09-19 18:39:08.13
>
> I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)
I meant (2) above.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with timestamp - Pls help
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
2003-09-19 15:39 ` Re: Problem with timestamp - Pls help Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-09-19 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-09-19 16:27 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)
> may get you what you want without having to rely on the text format.
If you don't want the time zone, there's also LOCALTIMESTAMP(n). See
http://www.postgresql.org/docs/7.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with timestamp - Pls help
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
@ 2003-09-19 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-09-19 15:42 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
"Kumar" <sgnerd@yahoo.com.sg> writes:
> I wanted to update one column of my table with now() or timestamp. And I wa=
> nt that timestamp of format ------------------------
> 2003-09-19 18:39:08.13
It sounds like you simply want to limit the fractional precision of the
value. Why don't you just declare the column as timestamp(2) ? All
this fooling around with substrings is inefficient and doesn't have much
to do with your real intent anyhow.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-09-19 16:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-19 13:14 Problem with timestamp - Pls help Kumar <sgnerd@yahoo.com.sg>
2003-09-19 14:03 ` Tomasz Myrta <jasiek@klaster.net>
2003-09-19 15:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 15:59 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 16:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-19 15:42 ` 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