Обсуждение: How to get the previous date?
<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>
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 />
Helo Madam,
Here is the query.
select now()+ interval '-1 day '
Regards,
ram
----- Original Message -----From: Shruthi ASent: Wednesday, October 14, 2009 3:10 PMSubject: [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
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
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
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
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)
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
"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
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
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
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