with standard date/time ranges, for example 1-24 -- so
there is no way how convert to anything like "31:57:52".
Karel
--
Karel Zak <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Mon, Jun 07, 2004 at 11:08:37AM +0200, Stefan Weiss wrote:
> On Monday, 07 June 2004 09:52, Karel Zak wrote:
> > http://www.postgresql.org/docs/7.4/static/functions-formatting.html
> >
> > Warning: to_char(interval, text) is depreca
ior for division with a negative input varies
> across machines. However I do see the bug on HPUX 10.20 with CVS tip.
Please, read PostgreSQL docs.
http://www.postgresql.org/docs/7.4/static/functions-formatting.html
Warning: to_char(interval, text) is deprecated and should not be
p://www.postgresql.org/docs/7.4/static/functions-string.html
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
The others PostgreSQL stuff which full parse (means check ranges)
date/time is less optimistic with this:
# se
On Thu, Oct 24, 2002 at 12:51:35PM +0200, Andreas Joseph Krogh wrote:
>
> janerik=# select created, to_char(created, 'WW') as week from session WHERE
> username IS NULL ORDER BY week;
Please, see docs and 'IW' (ISO week).
Karel
--
Karel Zak <[EMAIL PR
On Thu, Oct 03, 2002 at 10:46:19AM -0400, Thomas Good wrote:
>
> SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle
> SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL
SELECT TO_CHAR(now(),'HH:MI AM'); -- PostgreSQL
:-)
cast of numeric to text be the exact
> > equivalent of:
> >
> > SELECT btrim(to_char(numeric, '999,999,999,999.99'))
> > or similar.
Note: the btrim() call is needless. You can use Fill-Mode:
SELECT to_char(numeric, 'FM999,999,999,999.99');
9.99
> 9.99
> 9.99
> 9.99
> 9.99
> 9.99
> 9.99
> 9.99
> (10 rows)
>
> while under 7.2.1, you get the top 10 rows in *numerical* order:
>
> to_char
> -
> 278.78
> 261.07
> 240.25
> 180.24
> 173.26
>
ady in right now breeding 7.2 :-)
test=# SELECT to_char('5months 3sec 4h 1min'::interval, 'HH:MI:SS Mon');
to_char
--
04:01:03 May
(1 row)
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, Postgre
s parse queries like:
SVRMGR> select to_date('March 11, 1997', 'Month dd, ') from dual;
TO_DATE('
-
11-MAR-97
1 row selected.
.. well, I add it to my TODO for 7.3 (I plan rewrite several things
in to_* functions).
Karel
--
Karel Zak <
20
test=# select '2001-08-20'::date + '6months'::interval;
?column?
2002-02-20 00:00:00+01
(1 row)
..see docs about the "interval" datetype.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.
chars \012 \001 \002
> 5 | null
>11 | null \000 null
> (4 rows)
And what use some better encoding if you have a lot of binary chars
in data. For example base64, that code 2 chars to 3 instead \\000
that encode 1 char to 4.
operators: ~* or !~*
- case insensitive 'like'
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
$123,456.00
(1 row)
FM fill mode, skip blank spaces and zeroes (if not set '0' instead '9')
L currency symbol (from actual locales)
Right?
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP,
ser/datatype.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators2373.htm
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz
tegory_id
> Order by category_name
>
The PostgreSQL hasn't implemented Oracle's "walk tree" feature (yet???
-- speculate about it anyone?). If I good remember some discussion about
hierarchy system in tables was before now, see archives at postgresql.org.
or new features and I'm waiting for 7.2 devel. cycle and I'm spending
time with other things (PL/Python, the Mape project etc..).
If it's *really important* for you I can write it next week(s),
... of course, my time is limited :-)
May be try found some other solution.
docs about SPI interface or you can try use RULEs for this.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)
ime) where has each item like hours, minutes own field.
For some date/time operation is used Julian date (..etc) -- internaly PG
not works with strings for date/time.
I mean is too much difficult write a 'interval' to_char() version in
some procedural language without access to real (in
which is what "describe" for in mySQL.
As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.
Karel
would you suggest I convert an interval
The 'interval' version of to_char() isn't implemented -- may be in 7.2
(it's high in my TODO list:-)
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP,
On Tue, 6 Feb 2001 [EMAIL PROTECTED] wrote:
> Hi,
>
> I'm using a timestamp field called date_created. Whenever I select it
> I get:
>
> select date_created from tbl_user;
> date_created
>
> 2001-02-05 17:23:26-08
> 2001-02-05 17:45:39-08
> 2001-02-03 0
On Tue, 6 Feb 2001, J.Fernando Moyano wrote:
>
> Is there some way to do something like this ?? :
Yes,
select to_timestamp('hello 02-06-2001', '"hello "MM-DD-');
Karel
On Mon, 5 Feb 2001 [EMAIL PROTECTED] wrote:
> Ok, so there is actually two standards then. Is this documented
> anywhere? Is this is something that is going to change? I don't want
> to write and app and have things "break" during and upgrade :)
I mean you can be caseful. Not changes planne
On Mon, 5 Feb 2001, Ross J. Reedstrom wrote:
> On Mon, Feb 05, 2001 at 05:15:47PM +0100, Karel Zak wrote:
> >
> > test=# select date_part('dow','2001-02-11'::timestamp);
> > date_part
> > ---
> > 0
> >
> >
On Mon, 29 Jan 2001, Keith Perry wrote:
> Greetings,
>
> I notice some talk about date problems and interestingly enough planning
> out an application in which I will need to be able to manipulate dates.
> I notice however that there seems to be a discrepancy with the day or
> week in 7.0.3
>
On Wed, 17 Jan 2001, [EMAIL PROTECTED] wrote:
> E.g.
>
> create table mytable (created datetime);
> insert into mytable values ('01-01-2001');
> ...
> insert into mytable values ('01-31-2001');
>
> select created from mytable where date_part('dow', created) = 7 and
>date_part('month', created
> Date: Wed, 3 Jan 2001 11:11:36 -0700
> From: Michael Davis <[EMAIL PROTECTED]>
!> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
!> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
!> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
!> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
> Subject: [S
On Thu, 28 Dec 2000, Oliver Elphick wrote:
> [EMAIL PROTECTED] wrote:
> >Hi,
> >
> >I'm trying to figure out how to take a value like 3.68009074974387
> >(that is calculated from values in my database) and have PostgreSQL
> >hand me 3.68. Any suggestions would be appreciated.
>
> cast
On Tue, 19 Dec 2000, Jeff MacDonald wrote:
> hi folks..
>
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
And why not to_char()?
Karel
On Fri, 15 Dec 2000, Alessio Bragadini wrote:
> Sorry, I am trying to find my way in formatting timestamps for different
> timezones and I am a little confused.
>
> [ PostgreSQL 7.0.0 on alphaev6-dec-osf4.0f, compiled by cc ]
>
> Let's imagine
> CREATE TABLE tztest (id SERIAL, v TEXT, ts TIMES
On Fri, 15 Dec 2000, Marcin Mazurek wrote:
> Hi,
> Can anyone explain to me why this doesn't work. Seems to be some stupid (my)
> mistake:
>
> mtldb=# SELECT to_timestamp('05121445482000', 'MMDDHHMISS');
> to_timestamp
>
> 2000-05-12 14:45:48+02
> (1 row)
>
On Thu, 14 Dec 2000, Kovacs Zoltan Sandor wrote:
> Hi, this query gives different strange results:
>
> select to_char(now()::abstime,'YYMMDDHH24MI');
the result:
ERROR: to_char/to_number(): not unique decimal poit
is right, because we have two to_char():
to_char(int, text)
> > Hi,
>
> How can I use select ... for update to update limit to update what I
> select??
First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE
but transaction option. The PostgreSQL use row-locking for UPDATEed rows.
Standard SELECT ignore this lock, but SELECT FOR UPDATE wa
On Wed, 1 Nov 2000, pgsql-sql wrote:
> You can try the script I made for exporting all my Pg database.
> Ideas were borrowed from pg_dumplo-0.0.5.
> Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm"
> installed.
Why you re-write pg_dumplo to perl and not use directly it? Perl
/*
* Karel Zak * [EMAIL PROTECTED] * http://home.zf.jcu.cz/~zakkr/
* C, PostgreSQL, PHP, WWW, http://docs.linux.cz
*
*/
On Tue, 26 Sep 2000, Jerome Raupach wrote:
> CREATE TABLE TR (f1 FLOAT4, f2 INT4, f3 INT4) ;
>
> UPDATE TR SET
On Thu, 21 Sep 2000, Forest Wilkinson wrote:
> I have written a few Postgres extension functions in C, and want to modify
> some of them to return an int8. However, I don't see any int8 definition
> in postgres.h. (I have the 7.0.2 RPMs installed.) How should I
> accomplish this?
in the sou
On Tue, 5 Sep 2000, Poet/Joshua Drake wrote:
> Hello,
>
> I have heard of this infamous 8k limit. I have a couple of questions.
> 1. Does this mean that if I have a large object that I am inserting into a
> table, like an image it has to be 8k or less?
In current version is possible range 8--
On Wed, 23 Aug 2000, Jan Wieck wrote:
> Before you ask: The mentioned redesign will NOT be done for
> 7.1, and I'm not sure if we will be able to do it for 7.2
> yet.
I hope that 7.2 :-), my query/plan cache is still outside current
interest and if core developers not will wo
On Fri, 4 Aug 2000, Sandis wrote:
> I do it like this:
>
> SELECT datums FROM jaunumi
> WHERE date_part('year',datetime(datums)) = '2000' AND
> date_part('month',datetime(datums)) = '08';
>
> Where datums is a timestamp field.
Or (in 7.0):
SELECT datums FROM jaunum WHERE to_c
On Thu, 3 Aug 2000, Ange Michel POZZO wrote:
> Le jeu, 03 aoű 2000, Karel Zak a écrit :
> > > my question is how can i convert the numeric or the int4 value to
> > > a date value?
> >
> >
> > test=# select abstime(965293003);
> > abstime
> my question is how can i convert the numeric or the int4 value to
> a date value?
test=# select abstime(965293003);
abstime
2000-08-03 10:56:43+02
(1 row)
Karel
On Wed, 12 Jul 2000, Brian Powell wrote:
> Greetings,
>
> Working with PostGreSQL 7.02, I found the following problem:
PM/AM is already known bug and is already fixed in current CVS. In 7.1 it
will correct.
Karel
On Wed, 28 Jun 2000, Emils Klotins wrote:
> Hello,
>
> I have a table that has to have several fields with different names,
> but equal content. Sounds stupid, but it is because I have 2
> different programs querying the same table for user information and
> each of them uses differently nam
On Thu, 15 Jun 2000, The Hermit Hacker wrote:
> On Thu, 15 Jun 2000, Karel Zak wrote:
>
> >
> >
> > The PostgreSQL pg_dumplo 0.0.5; in brief:
> >
> > --- available at:
> >
> > * ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.
The PostgreSQL pg_dumplo 0.0.5; in brief:
--- available at:
* ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.5.tar.gz
* or will available in the PostgreSQL contrib tree in the CVS
--- features:
pg_dumplo [option]
-h --help
On Tue, 30 May 2000 [EMAIL PROTECTED] wrote:
> I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC.
> I am trying to use Pg/PLSQL.
>
> I even tried being conservative enough to copy the example code from
> http://www.postgresql.org/docs/user/c40874340.htm as follows:
> CREATE FUNCTION add
48 matches
Mail list logo
