How to get the previous date?

[Shruthi_iisc]

Hi,

I'm using Enterprise DB PostgresPlus version 8.3.
Is there is a simple function or command like previous_date(mydate) which when passed a particular date, simply returns the previous date? I tried

mydate - 1

but this doesnt work.

Thanks,
Shruthi

[Dmitriy Igrishin]

Hello.

current_date - 1 is the solution.

2009/10/14 Shruthi A <shruthi.iisc@...>

Hi,

I'm using Enterprise DB PostgresPlus version 8.3.
Is there is a simple function or command like previous_date(mydate) which when passed a particular date, simply returns the previous date? I tried

mydate - 1

but this doesnt work.

Thanks,
Shruthi

[Ramasubramanian G]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Helo Madam,

    Here is the query.

select now()+ interval '-1  day '

Regards,

ram

----- Original Message -----

From: shruthi.iisc@...

To: pgsql-sql@...

Sent: Wednesday, October 14, 2009 3:10 PM

Subject: [SQL] How to get the previous date?

Hi,

I'm using Enterprise DB PostgresPlus version 8.3.
Is there is a simple function or command like previous_date(mydate) which when passed a particular date, simply returns the previous date? I tried

mydate - 1

but this doesnt work.

Thanks,
Shruthi

[Oliveiros Cristina]

Some parts of this message have been removed. Learn more about Nabble's security policy.

 Hello, list.

 

I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres.

 

Does anyone have a clue on how this can be done?

 

Many thanx in advance,

 

Best,

Oliveiros

[msi77]

Try this:

select cast(mydate as date) -1

> Hi,
> I'm using Enterprise DB PostgresPlus version 8.3.
> Is there is a simple function or command like previous_date(mydate) which when passed a particular date, simply returns the previous date? I tried
> mydate - 1
> but this doesnt work.
> Thanks,
> Shruthi

Почта со встроенным плеером находится здесь: http://mail.yandex.ru/promo/new/wysiwyg   

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Adrian Klaver]

On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote:

>  Hello, list.
>
> I have a table with a varchar field that I would like to order by word, not
> by ordinal, which seems to be the default on postgres.
>
> Does anyone have a clue on how this can be done?
>
> Many thanx in advance,
>
> Best,
> Oliveiros

Can you show the SQL you are using?

--
Adrian Klaver
aklaver@...

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Jasen Betts-5]

On 2009-10-14, Shruthi A <shruthi.iisc@...> wrote:

> --0016e64698e4af821f0475e1f43d
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
>
> I'm using Enterprise DB PostgresPlus version 8.3.
> Is there is a simple function or command like previous_date(mydate) which
> when passed a particular date, simply returns the previous date? I tried
>
> mydate - 1
>
> but this doesnt work.

it should, if mydate is a date variable.

if it's some sort of timestamp more care is needed

 mytimestamp - interval'1 day'
 
also don't use interval'24h' unless you want 24h, because not all days are 24h

 select timestamptz'2009-09-28 00:30' - interval'24 h' ;
         ?column?        
         ------------------------
          2009-09-26 23:30:00+12
         
 (the interval crosses the local start of DST)
 
 

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Oliveiros Cristina]

Hello, Adrian.

Thank you for your email.

I am not using any SQL at this time, but I 'd like to understand
what is exactly the criterion that the ORDER BY uses to order text.

Giving you some background  :

I need to make an efficient join between data generated by my program
and data output by postgres and of course, it is mandatory that both lists
of records
be ordered using the same criterion.
I need to figure out what is exactly the criterion used by postgres so I can
mimic it on my
program. I thought it was ordinal, but it seems it isnt, as the @ symbol
comes before the 0 (zero).
According to ASCII numeric codes, it shouldn't


If any one can explain me exactly how the order by clause works on varchars,
I 'd really appreciate it.

I've already examined documentation carefully, but couldn't find it. Maybe I
looked on the wrong place...


Best,
Oliveiros

----- Original Message -----
From: "Adrian Klaver" <aklaver@...>
To: <pgsql-sql@...>
Cc: "Oliveiros C," <oliveiros.cristina@...>
Sent: Wednesday, October 14, 2009 9:54 PM
Subject: Re: [SQL] How to order varchar data by word

> On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote:
>>  Hello, list.
>>
>> I have a table with a varchar field that I would like to order by word,
>> not
>> by ordinal, which seems to be the default on postgres.
>>
>> Does anyone have a clue on how this can be done?
>>
>> Many thanx in advance,
>>
>> Best,
>> Oliveiros
>
> Can you show the SQL you are using?
>
> --
> Adrian Klaver
> aklaver@...
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Tom Lane-2]

"Oliveiros C," <oliveiros.cristina@...> writes:
> If any one can explain me exactly how the order by clause works on varchars,
> I 'd really appreciate it.

It sorts according to the sort order imposed by your locale setting
(see LC_COLLATE in particular).

A lot of locales have weird and wonderful rules that embody somebody's
notion of "dictionary order".  If you don't like it, try switching to
C locale.  Or you could learn enough about locale definitions to create
your own.

                        regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Oliveiros Cristina]

Hello, Tom.

Thank you for your e-mail

Even though I am not familiar with the exact procedure to switch to other
locales,
I'm gonna research this more deeply.

Anyway, I would appreciate if you could spare me some time and give me a
couple of pointers to this subject.

Again, thank you

Best,
Oliveiros

----- Original Message -----
From: "Tom Lane" <tgl@...>
To: "Oliveiros C," <oliveiros.cristina@...>
Cc: "Adrian Klaver" <aklaver@...>; <pgsql-sql@...>
Sent: Monday, October 19, 2009 6:32 PM
Subject: Re: [SQL] How to order varchar data by word

> "Oliveiros C," <oliveiros.cristina@...> writes:
>> If any one can explain me exactly how the order by clause works on
>> varchars,
>> I 'd really appreciate it.
>
> It sorts according to the sort order imposed by your locale setting
> (see LC_COLLATE in particular).
>
> A lot of locales have weird and wonderful rules that embody somebody's
> notion of "dictionary order".  If you don't like it, try switching to
> C locale.  Or you could learn enough about locale definitions to create
> your own.
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql 

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Adrian Klaver]

On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote:

> Hello, Tom.
>
> Thank you for your e-mail
>
> Even though I am not familiar with the exact procedure to switch to other
> locales,
> I'm gonna research this more deeply.
>
> Anyway, I would appreciate if you could spare me some time and give me a
> couple of pointers to this subject.
>
> Again, thank you
>
> Best,
> Oliveiros
>

A good place to start is:
http://www.postgresql.org/docs/8.4/interactive/locale.html
Check out the Further Reading section at:
http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078

--
Adrian Klaver
aklaver@...

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Oliveiros Cristina]

Hello, Adrian, thanks for the links.

I enjoyed the technique on the end of the second page (on the version 8.3
with comments), the shadow column trick.

Given that LC_COLLATE is immutable, unless a new database is created, I
guess I'll give the shadow column a try.

Best,
Oliveiros

----- Original Message -----
From: "Adrian Klaver" <aklaver@...>
To: "Oliveiros C," <oliveiros.cristina@...>
Cc: "Tom Lane" <tgl@...>; <pgsql-sql@...>
Sent: Tuesday, October 20, 2009 2:46 PM
Subject: Re: [SQL] How to order varchar data by word

> On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote:
>> Hello, Tom.
>>
>> Thank you for your e-mail
>>
>> Even though I am not familiar with the exact procedure to switch to other
>> locales,
>> I'm gonna research this more deeply.
>>
>> Anyway, I would appreciate if you could spare me some time and give me a
>> couple of pointers to this subject.
>>
>> Again, thank you
>>
>> Best,
>> Oliveiros
>>
>
> A good place to start is:
> http://www.postgresql.org/docs/8.4/interactive/locale.html
> Check out the Further Reading section at:
> http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078
>
> --
> Adrian Klaver
> aklaver@...

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql