Thread: How to get the previous date?

How to get the previous date?

From

Shruthi A

Date:

14 October 2009, 09:40:49

<span class="postbody" style="font-family: courier new,monospace;">Hi, <br /><br /> I'm using Enterprise DB
PostgresPlusversion 8.3. <br /> Is there is a simple function or command like previous_date(mydate) which when passed a
particulardate, simply returns the previous date? I tried <br /><br /> mydate - 1 <br /><br /> but this doesnt work.
<br/><br /> Thanks, <br /> Shruthi</span>

Re: How to get the previous date?

From

Dmitriy Igrishin

Date:

14 October 2009, 09:44:03

Hello.<br /><br />current_date - 1 is the solution.<br /><br /><div class="gmail_quote">2009/10/14 Shruthi A <span
dir="ltr"><<ahref="mailto:shruthi.iisc@gmail.com">shruthi.iisc@gmail.com</a>></span><br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><spanstyle="font-family: courier new,monospace;">Hi, <br /><br /> I'm using Enterprise DB PostgresPlus version
8.3.<br /> Is there is a simple function or command like previous_date(mydate) which when passed a particular date,
simplyreturns the previous date? I tried <br /><br /> mydate - 1 <br /><br /> but this doesnt work. <br /><br />
Thanks,<br /><font color="#888888"> Shruthi</font></span></blockquote></div><br />

Re: How to get the previous date?

From

"ramasubramanian"

Date:

14 October 2009, 10:07:44

Helo Madam,

Here is the query.

select now()+ interval '-1 day '

Regards,

ram

----- Original Message -----
From: Shruthi A
To: pgsql-sql@postgresql.org
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

How to order varchar data by word

From

"Oliveiros C,"

Date:

14 October 2009, 14:13:35

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

Re: How to get the previous date?

From

msi77

Date:

14 October 2009, 20:49:12

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
returnsthe previous date? I tried 
 
> mydate - 1 
> but this doesnt work. 
> Thanks, 
> Shruthi 

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

Re: How to order varchar data by word

From

Adrian Klaver

Date:

14 October 2009, 20:54:39

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@comcast.net

Re: How to get the previous date?

From

Jasen Betts

Date:

17 October 2009, 11:42:01

On 2009-10-14, Shruthi A <shruthi.iisc@gmail.com> 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)

Re: How to order varchar data by word

From

"Oliveiros C,"

Date:

19 October 2009, 17:08:09

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@comcast.net>
To: <pgsql-sql@postgresql.org>
Cc: "Oliveiros C," <oliveiros.cristina@marktest.pt>
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@comcast.net
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Re: How to order varchar data by word

From

Tom Lane

Date:

19 October 2009, 17:33:03

"Oliveiros C," <oliveiros.cristina@marktest.pt> 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

Re: How to order varchar data by word

From

"Oliveiros C,"

Date:

20 October 2009, 13:39:43

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@sss.pgh.pa.us>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: "Adrian Klaver" <aklaver@comcast.net>; <pgsql-sql@postgresql.org>
Sent: Monday, October 19, 2009 6:32 PM
Subject: Re: [SQL] How to order varchar data by word


> "Oliveiros C," <oliveiros.cristina@marktest.pt> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Re: How to order varchar data by word

From

Adrian Klaver

Date:

20 October 2009, 13:47:00

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@comcast.net

Re: How to order varchar data by word

From

"Oliveiros C,"

Date:

20 October 2009, 15:54:58

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@comcast.net>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-sql@postgresql.org>
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@comcast.net