pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] US Date Style
8+ messages / 6 participants
[nested] [flat]
* US Date Style
@ 2000-04-20 16:20 Jason Earl <jdearl@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jason Earl @ 2000-04-20 16:20 UTC (permalink / raw)
To: pgsql-sql
Note: I sent this to the pgsql-general list from
another account
yesterday, but it hasn't been answered. My apologies
if you see it
twice. On the other hand, I have taken some time to
proofread a bit,
so perhaps the punctuation won't be quite as bad.
I recently upgraded the PostgreSQL database on one of
my Debian Linux
boxes to postgresql-7.0-0.beta3-1 in preparation for
the grand
switchover to version 7.0. So far I am quite
impressed, but I have
had a little bit of a problem setting the PGDATESTYLE
correctly. For
historical reasons I would like to continue using the
US datestyle
(eg. Sat Jan 01 07:09:19 2000 MDT).
I have tried setting the variable in
/etc/postgresql/postmaster.init
and no joy. I have also tried to set the value of
PGDATESTYLE
manually in psql using:
processdata=>\set PGDATESTYLE US
When I do this I am able to then check the variables
that are set and
I get:
processdata=> \set
VERSION = 'PostgreSQL 7.0.0 on i686-pc-linux-gnu,
compiled by gcc 2.95.2'
DBNAME = 'processdata'
USER = 'earlj'
HOST = 'nampadata'
PORT = '5432'
ENCODING = 'SQL_ASCII'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
PGDATESTYLE = 'US'
Unfortunately, when I test it out:
processdata=> select 'now'::datetime;
?column?
------------------------
2000-04-19 14:09:06-06
(1 row)
I still get the ISO format. So what do I need to do
to get my US
datestyle back?
Sorry to bother,
Jason Earl
__________________________________________________
Do You Yahoo!?
Send online invitations with Yahoo! Invites.
http://invites.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: US Date Style
@ 2000-04-20 16:42 Jason Earl <jdearl@yahoo.com>
2000-04-20 18:14 ` Re: US Date Style Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jason Earl @ 2000-04-20 16:42 UTC (permalink / raw)
To: pgsql-sql
With a little bit of grepping through the docs I have
found something that will mostly work for me. I
simply need to have each client issue a:
SET DATESTYLE TO 'Postgres';
This works just fine, but (of course) I would rather
have this set up as the 'default.' Does anyone know
how to do this?
Thanks again,
Jason
--- Jason Earl <jdearl@yahoo.com> wrote:
> Note: I sent this to the pgsql-general list from
> another account
> yesterday, but it hasn't been answered. My
> apologies
> if you see it
> twice. On the other hand, I have taken some time to
> proofread a bit,
> so perhaps the punctuation won't be quite as bad.
>
> I recently upgraded the PostgreSQL database on one
> of
> my Debian Linux
> boxes to postgresql-7.0-0.beta3-1 in preparation for
> the grand
> switchover to version 7.0. So far I am quite
> impressed, but I have
> had a little bit of a problem setting the
> PGDATESTYLE
> correctly. For
> historical reasons I would like to continue using
> the
> US datestyle
> (eg. Sat Jan 01 07:09:19 2000 MDT).
>
> I have tried setting the variable in
> /etc/postgresql/postmaster.init
> and no joy. I have also tried to set the value of
> PGDATESTYLE
> manually in psql using:
>
> processdata=>\set PGDATESTYLE US
>
> When I do this I am able to then check the variables
> that are set and
> I get:
>
> processdata=> \set
> VERSION = 'PostgreSQL 7.0.0 on i686-pc-linux-gnu,
> compiled by gcc 2.95.2'
> DBNAME = 'processdata'
> USER = 'earlj'
> HOST = 'nampadata'
> PORT = '5432'
> ENCODING = 'SQL_ASCII'
> PROMPT1 = '%/%R%# '
> PROMPT2 = '%/%R%# '
> PROMPT3 = '>> '
> HISTSIZE = '500'
> PGDATESTYLE = 'US'
>
> Unfortunately, when I test it out:
>
> processdata=> select 'now'::datetime;
> ?column?
> ------------------------
> 2000-04-19 14:09:06-06
> (1 row)
>
> I still get the ISO format. So what do I need to do
> to get my US
> datestyle back?
>
> Sorry to bother,
> Jason Earl
>
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
>
__________________________________________________
Do You Yahoo!?
Send online invitations with Yahoo! Invites.
http://invites.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: US Date Style
2000-04-20 16:42 Re: US Date Style Jason Earl <jdearl@yahoo.com>
@ 2000-04-20 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2000-04-20 18:14 UTC (permalink / raw)
To: Jason Earl <jdearl@yahoo.com>; +Cc: pgsql-sql
Jason Earl <jdearl@yahoo.com> writes:
> With a little bit of grepping through the docs I have
> found something that will mostly work for me. I
> simply need to have each client issue a:
> SET DATESTYLE TO 'Postgres';
> This works just fine, but (of course) I would rather
> have this set up as the 'default.' Does anyone know
> how to do this?
PGDATESTYLE (note spelling) as an environment variable in the
environment of either the postmaster or the client application
will do it. If you start the postmaster with that in its environment,
it becomes the default setting for all backends in that installation.
If you start a (libpq-based) client with that in its environment,
libpq will issue the SET DATESTYLE command for you during connection
startup.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: US Date Style
@ 2000-04-20 18:12 Jason Earl <jdearl@yahoo.com>
2000-04-27 17:57 ` [SQL] US Date Style ditilupi <gme@ufba.br>
0 siblings, 1 reply; 8+ messages in thread
From: Jason Earl @ 2000-04-20 18:12 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Web Manager <web@inter-resa.com>
Thanks to <web Manager> and the PostgreSQL docs I have
found out what I was doing wrong. Actually the fix
was so dead simple that I am more than a little
embarrased to share.
But here goes...
The Debian package of PostgreSQL has a nifty
configuration file /etc/postgresql/postmaster.init .
Basically this file let's you customize just about any
PostgreSQL parameter that is configurable at run time
and worth fiddling with. I read the config file and
decided that setting:
PGDATESTYLE=US
Should do what I needed. Unfortunately what I
actually wanted was:
PGDATESTYLE=POSTGRES
Thanks to web@inter-resa.com, and special thanks to
the PostgreSQL team for their continued efforts.
7.0 Rocks,
Jason Earl
--- Jason Earl <jdearl@yahoo.com> wrote:
> With a little bit of grepping through the docs I
> have
> found something that will mostly work for me. I
> simply need to have each client issue a:
>
> SET DATESTYLE TO 'Postgres';
>
> This works just fine, but (of course) I would rather
> have this set up as the 'default.' Does anyone know
> how to do this?
>
> Thanks again,
> Jason
>
> --- Jason Earl <jdearl@yahoo.com> wrote:
> > Note: I sent this to the pgsql-general list from
> > another account
> > yesterday, but it hasn't been answered. My
> > apologies
> > if you see it
> > twice. On the other hand, I have taken some time
> to
> > proofread a bit,
> > so perhaps the punctuation won't be quite as bad.
> >
> > I recently upgraded the PostgreSQL database on one
> > of
> > my Debian Linux
> > boxes to postgresql-7.0-0.beta3-1 in preparation
> for
> > the grand
> > switchover to version 7.0. So far I am quite
> > impressed, but I have
> > had a little bit of a problem setting the
> > PGDATESTYLE
> > correctly. For
> > historical reasons I would like to continue using
> > the
> > US datestyle
> > (eg. Sat Jan 01 07:09:19 2000 MDT).
> >
> > I have tried setting the variable in
> > /etc/postgresql/postmaster.init
> > and no joy. I have also tried to set the value of
> > PGDATESTYLE
> > manually in psql using:
> >
> > processdata=>\set PGDATESTYLE US
> >
> > When I do this I am able to then check the
> variables
> > that are set and
> > I get:
> >
> > processdata=> \set
> > VERSION = 'PostgreSQL 7.0.0 on i686-pc-linux-gnu,
> > compiled by gcc 2.95.2'
> > DBNAME = 'processdata'
> > USER = 'earlj'
> > HOST = 'nampadata'
> > PORT = '5432'
> > ENCODING = 'SQL_ASCII'
> > PROMPT1 = '%/%R%# '
> > PROMPT2 = '%/%R%# '
> > PROMPT3 = '>> '
> > HISTSIZE = '500'
> > PGDATESTYLE = 'US'
> >
> > Unfortunately, when I test it out:
> >
> > processdata=> select 'now'::datetime;
> > ?column?
> > ------------------------
> > 2000-04-19 14:09:06-06
> > (1 row)
> >
> > I still get the ISO format. So what do I need to
> do
> > to get my US
> > datestyle back?
> >
> > Sorry to bother,
> > Jason Earl
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send online invitations with Yahoo! Invites.
> > http://invites.yahoo.com
> >
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
>
__________________________________________________
Do You Yahoo!?
Send online invitations with Yahoo! Invites.
http://invites.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* [SQL] US Date Style
2000-04-20 18:12 Re: US Date Style Jason Earl <jdearl@yahoo.com>
@ 2000-04-27 17:57 ` ditilupi <gme@ufba.br>
1970-01-01 00:00 ` Re: [SQL] US Date Style Manuel Lemos <mlemos@acm.org>
2000-04-27 19:48 ` Re: [SQL] US Date Style Karel Zak <zakkr@zf.jcu.cz>
2000-04-27 19:56 ` Re: [SQL] US Date Style Steve Wolfe <steve@iboats.com>
0 siblings, 3 replies; 8+ messages in thread
From: ditilupi @ 2000-04-27 17:57 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi people,
I'm having this problem with date fields. I'm using the Postgresql7.0beta4
and programming in PHP3. When I set the environment variable PGDATESTYLE
to "NonEuropean,SQL" it works, but only if I'm in the Pgsql environment.
When I access through the PHP3 it doesn't work.
Does anybody have any suggestion that could help me?
Thanks,
Gabriela.
On Thu, 20 Apr 2000, Jason Earl wrote:
> Thanks to <web Manager> and the PostgreSQL docs I have
> found out what I was doing wrong. Actually the fix
> was so dead simple that I am more than a little
> embarrased to share.
>
> But here goes...
>
> The Debian package of PostgreSQL has a nifty
> configuration file /etc/postgresql/postmaster.init .
> Basically this file let's you customize just about any
> PostgreSQL parameter that is configurable at run time
> and worth fiddling with. I read the config file and
> decided that setting:
>
> PGDATESTYLE=US
>
> Should do what I needed. Unfortunately what I
> actually wanted was:
>
> PGDATESTYLE=POSTGRES
>
> Thanks to web@inter-resa.com, and special thanks to
> the PostgreSQL team for their continued efforts.
>
> 7.0 Rocks,
> Jason Earl
>
> --- Jason Earl <jdearl@yahoo.com> wrote:
> > With a little bit of grepping through the docs I
> > have
> > found something that will mostly work for me. I
> > simply need to have each client issue a:
> >
> > SET DATESTYLE TO 'Postgres';
> >
> > This works just fine, but (of course) I would rather
> > have this set up as the 'default.' Does anyone know
> > how to do this?
> >
> > Thanks again,
> > Jason
> >
> > --- Jason Earl <jdearl@yahoo.com> wrote:
> > > Note: I sent this to the pgsql-general list from
> > > another account
> > > yesterday, but it hasn't been answered. My
> > > apologies
> > > if you see it
> > > twice. On the other hand, I have taken some time
> > to
> > > proofread a bit,
> > > so perhaps the punctuation won't be quite as bad.
> > >
> > > I recently upgraded the PostgreSQL database on one
> > > of
> > > my Debian Linux
> > > boxes to postgresql-7.0-0.beta3-1 in preparation
> > for
> > > the grand
> > > switchover to version 7.0. So far I am quite
> > > impressed, but I have
> > > had a little bit of a problem setting the
> > > PGDATESTYLE
> > > correctly. For
> > > historical reasons I would like to continue using
> > > the
> > > US datestyle
> > > (eg. Sat Jan 01 07:09:19 2000 MDT).
> > >
> > > I have tried setting the variable in
> > > /etc/postgresql/postmaster.init
> > > and no joy. I have also tried to set the value of
> > > PGDATESTYLE
> > > manually in psql using:
> > >
> > > processdata=>\set PGDATESTYLE US
> > >
> > > When I do this I am able to then check the
> > variables
> > > that are set and
> > > I get:
> > >
> > > processdata=> \set
> > > VERSION = 'PostgreSQL 7.0.0 on i686-pc-linux-gnu,
> > > compiled by gcc 2.95.2'
> > > DBNAME = 'processdata'
> > > USER = 'earlj'
> > > HOST = 'nampadata'
> > > PORT = '5432'
> > > ENCODING = 'SQL_ASCII'
> > > PROMPT1 = '%/%R%# '
> > > PROMPT2 = '%/%R%# '
> > > PROMPT3 = '>> '
> > > HISTSIZE = '500'
> > > PGDATESTYLE = 'US'
> > >
> > > Unfortunately, when I test it out:
> > >
> > > processdata=> select 'now'::datetime;
> > > ?column?
> > > ------------------------
> > > 2000-04-19 14:09:06-06
> > > (1 row)
> > >
> > > I still get the ISO format. So what do I need to
> > do
> > > to get my US
> > > datestyle back?
> > >
> > > Sorry to bother,
> > > Jason Earl
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Send online invitations with Yahoo! Invites.
> > > http://invites.yahoo.com
> > >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send online invitations with Yahoo! Invites.
> > http://invites.yahoo.com
> >
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] US Date Style
2000-04-20 18:12 Re: US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-27 17:57 ` [SQL] US Date Style ditilupi <gme@ufba.br>
@ 1970-01-01 00:00 ` Manuel Lemos <mlemos@acm.org>
2 siblings, 0 replies; 8+ messages in thread
From: Manuel Lemos @ 1970-01-01 00:00 UTC (permalink / raw)
To: ditilupi <gme@ufba.br>; +Cc: pgsql-general@postgresql.org
Hello ditilupi,
On 27-Apr-00 15:57:01, you wrote:
>I'm having this problem with date fields. I'm using the Postgresql7.0beta4
>and programming in PHP3. When I set the environment variable PGDATESTYLE
>to "NonEuropean,SQL" it works, but only if I'm in the Pgsql environment.
>When I access through the PHP3 it doesn't work.
You need to add a PHP statement like Putenv("PGDATESTYLE=ISO"); before
connecting with pg_connect or pg_pconnect. Beware that there is a bug in
PHP 4 RC1 that resets this environment variable.
While you are at it, you may want to try Metabase, a database abstraction
package in PHP that comes with a PostgreSQL driver. Among other things
it assures that date fields are always returned in the ISO 8601 standard
format (YYYY-MM-DD).
This format is easier to manage because the fields always occupy fixed
prositions and you may use PHP text sorting and comparision functions to
sort (sort) or compare (strcmp) dates without having to convert them to
time stamp integer values.
Metabase is free. Look here to download it:
http://phpclasses.UpperDesign.com/browse.html/package/20
Regards,
Manuel Lemos
Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] US Date Style
2000-04-20 18:12 Re: US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-27 17:57 ` [SQL] US Date Style ditilupi <gme@ufba.br>
@ 2000-04-27 19:48 ` Karel Zak <zakkr@zf.jcu.cz>
2 siblings, 0 replies; 8+ messages in thread
From: Karel Zak @ 2000-04-27 19:48 UTC (permalink / raw)
To: ditilupi <gme@ufba.br>; +Cc: pgsql-general@postgresql.org
On Thu, 27 Apr 2000, ditilupi wrote:
>
> Hi people,
>
> I'm having this problem with date fields. I'm using the Postgresql7.0beta4
> and programming in PHP3. When I set the environment variable PGDATESTYLE
> to "NonEuropean,SQL" it works, but only if I'm in the Pgsql environment.
> When I access through the PHP3 it doesn't work.
>
> Does anybody have any suggestion that could help me?
In 7.0: to_char() for full date/time format control. For more details see
the PG docs.
Karel
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] US Date Style
2000-04-20 18:12 Re: US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-27 17:57 ` [SQL] US Date Style ditilupi <gme@ufba.br>
@ 2000-04-27 19:56 ` Steve Wolfe <steve@iboats.com>
2 siblings, 0 replies; 8+ messages in thread
From: Steve Wolfe @ 2000-04-27 19:56 UTC (permalink / raw)
To: pgsql-general@postgresql.org
> I'm having this problem with date fields. I'm using the
Postgresql7.0beta4
> and programming in PHP3. When I set the environment variable PGDATESTYLE
> to "NonEuropean,SQL" it works, but only if I'm in the Pgsql environment.
> When I access through the PHP3 it doesn't work.
>
> Does anybody have any suggestion that could help me?
When you access is via the PHP interface (which, I assume, is running as
a CGI program), then you are not running as pgsql, and so any settings
you've put into pgsql's startup scripts will not take effect. Determine
which user is actually executing the code when accessed normally (hopefully
you're using some mechanism to execute scripts as the owner, in which case
it will be the owner of the file. Otherwise it will be the user that the
web server runs as.) Then, make the appropriate settings in the
appropriate scripts for that user.
steve
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2000-04-27 19:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-20 16:20 US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-20 16:42 Re: US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-20 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-04-20 18:12 Re: US Date Style Jason Earl <jdearl@yahoo.com>
2000-04-27 17:57 ` ditilupi <gme@ufba.br>
1970-01-01 00:00 ` Manuel Lemos <mlemos@acm.org>
2000-04-27 19:48 ` Karel Zak <zakkr@zf.jcu.cz>
2000-04-27 19:56 ` Steve Wolfe <steve@iboats.com>
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