pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate question
15+ messages / 12 participants
[nested] [flat]
* Date question
@ 2001-03-06 19:19 Boulat Khakimov <boulat@inet-interactif.com>
0 siblings, 4 replies; 15+ messages in thread
From: Boulat Khakimov @ 2001-03-06 19:19 UTC (permalink / raw)
To: pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
Hi,
Im a little bit stuck here.
Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
year from now.
So for example today is '2001-03-06' I need to get date 12 months from
now
which will be '2002-03-06' in todays case...
In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
doesnt work in PG.
Regards,
Boulat Khakimov
--
Nothing Like the Sun
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: Date question
@ 2001-03-06 19:25 Mike Mascari <mascarm@mascari.com>
0 siblings, 0 replies; 15+ messages in thread
From: Mike Mascari @ 2001-03-06 19:25 UTC (permalink / raw)
To: 'Boulat Khakimov' <boulat@inet-interactif.com>; pgsql-sql; psql-novice@postgresql.org <psql-novice@postgresql.org>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
How about:
SELECT '2001-03-06'::timestamp + '1 Year';
Hope that helps,
Mike Mascari
-----Original Message-----
From: Boulat Khakimov [SMTP:boulat@inet-interactif.com]
Sent: Tuesday, March 06, 2001 2:20 PM
To: pgsql-sql@postgresql.org; psql-novice@postgresql.org; pgsql-general@postgresql.org
Subject: [GENERAL] Date question
Hi,
Im a little bit stuck here.
Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
year from now.
So for example today is '2001-03-06' I need to get date 12 months from
now
which will be '2002-03-06' in todays case...
In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
doesnt work in PG.
Regards,
Boulat Khakimov
--
Nothing Like the Sun
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] Date question
@ 2001-03-06 19:26 Michael Fork <mfork@toledolink.com>
parent: Boulat Khakimov <boulat@inet-interactif.com>
3 siblings, 0 replies; 15+ messages in thread
From: Michael Fork @ 2001-03-06 19:26 UTC (permalink / raw)
To: Boulat Khakimov <boulat@inet-interactif.com>; +Cc: pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
This will do it:
mfork=# SELECT to_char(now() + '1 Year'::interval, 'YYYY-MM-DD');
to_char
------------
2002-03-06
(1 row)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Tue, 6 Mar 2001, Boulat Khakimov wrote:
> Hi,
>
> Im a little bit stuck here.
>
> Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
> year from now.
> So for example today is '2001-03-06' I need to get date 12 months from
> now
> which will be '2002-03-06' in todays case...
>
> In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
> doesnt work in PG.
>
>
> Regards,
> Boulat Khakimov
>
>
> --
> Nothing Like the Sun
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: [SQL] Date question
@ 2001-03-06 19:30 Francis Solomon <francis@stellison.co.uk>
parent: Boulat Khakimov <boulat@inet-interactif.com>
3 siblings, 1 reply; 15+ messages in thread
From: Francis Solomon @ 2001-03-06 19:30 UTC (permalink / raw)
To: Boulat Khakimov <boulat@inet-interactif.com>; pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
Hi Boulat,
stasis=# select (now() + '1 year')::date;
?column?
------------
2002-03-06
(1 row)
Hope this helps
Francis
> Hi,
>
> Im a little bit stuck here.
>
> Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
> year from now.
> So for example today is '2001-03-06' I need to get date 12 months from
> now
> which will be '2002-03-06' in todays case...
>
> In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
> doesnt work in PG.
>
>
> Regards,
> Boulat Khakimov
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Date question
@ 2001-03-06 19:40 Peter Eisentraut <peter_e@gmx.net>
parent: Boulat Khakimov <boulat@inet-interactif.com>
3 siblings, 0 replies; 15+ messages in thread
From: Peter Eisentraut @ 2001-03-06 19:40 UTC (permalink / raw)
To: Boulat Khakimov <boulat@inet-interactif.com>; +Cc: pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
Boulat Khakimov writes:
> Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
> year from now.
> So for example today is '2001-03-06' I need to get date 12 months from
> now
> which will be '2002-03-06' in todays case...
>
> In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
> doesnt work in PG.
How about CURRENT_DATE + INTERVAL '12 months'?
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] Date question
@ 2001-03-06 20:02 Jie Liang <jliang@ipinc.com>
parent: Boulat Khakimov <boulat@inet-interactif.com>
3 siblings, 0 replies; 15+ messages in thread
From: Jie Liang @ 2001-03-06 20:02 UTC (permalink / raw)
To: Boulat Khakimov <boulat@inet-interactif.com>; +Cc: pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
you can say:
(now() + '1year'::timespan)::date
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Tue, 6 Mar 2001, Boulat Khakimov wrote:
> Hi,
>
> Im a little bit stuck here.
>
> Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
> year from now.
> So for example today is '2001-03-06' I need to get date 12 months from
> now
> which will be '2002-03-06' in todays case...
>
> In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
> doesnt work in PG.
>
>
> Regards,
> Boulat Khakimov
>
>
> --
> Nothing Like the Sun
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] Date question
@ 2001-03-06 20:37 clayton cottingham <drfrog@smartt.com>
parent: Francis Solomon <francis@stellison.co.uk>
0 siblings, 0 replies; 15+ messages in thread
From: clayton cottingham @ 2001-03-06 20:37 UTC (permalink / raw)
To: Francis Solomon <francis@stellison.co.uk>; +Cc: Boulat Khakimov <boulat@inet-interactif.com>; pgsql-sql; psql-novice@postgresql.org; pgsql-general@postgresql.org
Francis Solomon wrote:
>
> Hi Boulat,
>
> stasis=# select (now() + '1 year')::date;
> ?column?
> ------------
> 2002-03-06
> (1 row)
>
> Hope this helps
>
> Francis
>
> > Hi,
> >
> > Im a little bit stuck here.
> >
> > Does anyone know how to get date in format 'YYYY-MM-DD' of a date one
> > year from now.
> > So for example today is '2001-03-06' I need to get date 12 months from
> > now
> > which will be '2002-03-06' in todays case...
> >
> > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that
> > doesnt work in PG.
> >
> >
> > Regards,
> > Boulat Khakimov
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
i dunno about you but i like this syntax better than the old :: ones
select date(now()+ '1 year');
^ permalink raw reply [nested|flat] 15+ messages in thread
* date question
@ 2003-06-13 11:44 javier garcia - CEBAS <rn001@cebas.csic.es>
0 siblings, 2 replies; 15+ messages in thread
From: javier garcia - CEBAS @ 2003-06-13 11:44 UTC (permalink / raw)
To: pgsql-sql
Hi;
I've got a table in the way:
year | month | day | est7237 | est7238 | est7239 | est7250
------+-------+-----+---------+---------+---------+---------
2002 | 9 | 1 | | | |
2002 | 9 | 2 | | | |
2002 | 9 | 3 | | | |
...
This table is:
murciadb=# \d series_lluvia_completas
Table "series_lluvia_completas"
Column | Type | Modifiers
---------+---------+-----------
year | integer |
month | integer |
day | integer |
est7237 | real |
est7238 | real |
est7239 | real |
est7250 | real |
And I've got another related tables with a date field. I need to compare the
dates field in the other tables with "year", "month", "day" in this table.
Is there a way to create a table from this table, directly in Postgres in
which a date field is created based in the values of "year", "month", "day"
in this table?
Thanks and regards
Javier
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2003-06-16 17:24 Bruno Wolff III <bruno@wolff.to>
parent: javier garcia - CEBAS <rn001@cebas.csic.es>
1 sibling, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2003-06-16 17:24 UTC (permalink / raw)
To: javier garcia - CEBAS <rn001@cebas.csic.es>; +Cc: pgsql-sql
On Fri, Jun 13, 2003 at 13:44:09 +0200,
javier garcia - CEBAS <rn001@cebas.csic.es> wrote:
>
> And I've got another related tables with a date field. I need to compare the
> dates field in the other tables with "year", "month", "day" in this table.
>
> Is there a way to create a table from this table, directly in Postgres in
> which a date field is created based in the values of "year", "month", "day"
> in this table?
Yes. You could concatenate the values for year, month and day and cast the
resulting string to date.
In the long run you might be better storing the dates as dates in the table
and extracting the year, month and day when reporting (or perhaps using
a view to add them as additional columns).
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2003-06-17 18:09 Peter Eisentraut <peter_e@gmx.net>
parent: javier garcia - CEBAS <rn001@cebas.csic.es>
1 sibling, 1 reply; 15+ messages in thread
From: Peter Eisentraut @ 2003-06-17 18:09 UTC (permalink / raw)
To: javier garcia - CEBAS <rn001@cebas.csic.es>; +Cc: pgsql-sql
javier garcia - CEBAS writes:
> Is there a way to create a table from this table, directly in Postgres in
> which a date field is created based in the values of "year", "month", "day"
> in this table?
year * interval '1 year' + month * interval '1 month' + day * interval '1 day'
This results in a timestamp value that you can compare to or assign to a
date value.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2003-06-20 17:33 javier garcia - CEBAS <rn001@cebas.csic.es>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 15+ messages in thread
From: javier garcia - CEBAS @ 2003-06-20 17:33 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
Hi all;
Peter, thank you very much for your help. Just a little thing. I've done as
you say:
CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month *
interval '1 month' + day * interval '1 day') AS
fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha;
I see that the obtained new field is "interval" type:
Column | Type | Modifiers
---------+----------+-----------
fecha | interval |
est7237 | real |
est7238 | real |
....
And I've tried to use this result to be compared with my other table in which
"fecha" is "date" type. The result is that the query halts with no result; I
guess that it is because it tries to compare different data types.
Is it possible to cast the "interval" obtained type into a "date" one in the
creation of the mentioned table? (I've looked it in the documentation, but I
can't find the answer)
Thanks and regards,
Javier
............................
El Mar 17 Jun 2003 20:09, escribió:
> javier garcia - CEBAS writes:
> > Is there a way to create a table from this table, directly in Postgres in
> > which a date field is created based in the values of "year", "month",
> > "day" in this table?
>------------------------------------------------------------------------------------------------------------------
> year * interval '1 year' + month * interval '1 month' + day * interval '1
> day'
>
> This results in a timestamp value that you can compare to or assign to a
> date value.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2003-06-22 11:35 Bruno Wolff III <bruno@wolff.to>
parent: javier garcia - CEBAS <rn001@cebas.csic.es>
0 siblings, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2003-06-22 11:35 UTC (permalink / raw)
To: javier garcia - CEBAS <rn001@cebas.csic.es>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
On Fri, Jun 20, 2003 at 19:33:35 +0200,
javier garcia - CEBAS <rn001@cebas.csic.es> wrote:
> Hi all;
>
> Peter, thank you very much for your help. Just a little thing. I've done as
> you say:
>
> CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month *
> interval '1 month' + day * interval '1 day') AS
> fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha;
It would probably be better to use a view instead of copying the data
to another table. That way you don't need to worry about the data
getting out of sync.
> And I've tried to use this result to be compared with my other table in which
> "fecha" is "date" type. The result is that the query halts with no result; I
> guess that it is because it tries to compare different data types.
It wouldn't be because of trying to compare a date and an interval.
You would get an error message instead.
> Is it possible to cast the "interval" obtained type into a "date" one in the
> creation of the mentioned table? (I've looked it in the documentation, but I
> can't find the answer)
Not really. You could add the interval values to a base date, but that may
or may not make sense depending on your application.
^ permalink raw reply [nested|flat] 15+ messages in thread
* date question
@ 2005-11-03 16:17 Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
0 siblings, 2 replies; 15+ messages in thread
From: Judith Altamirano Figueroa @ 2005-11-03 16:17 UTC (permalink / raw)
To: pgsql-sql
Hi everybody, in Postgres 7.0.2 I have the next query:
SELECT * from clientes_proceso where fecha_mod::date <= now() -1;
but in version 8.0.1 returns the next error:
ERROR: The operator doesn't exist: timestamp with time zone - integer
How can drop a day to now()??
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2005-11-03 17:22 Michael Glaesemann <grzm@myrealbox.com>
parent: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
1 sibling, 0 replies; 15+ messages in thread
From: Michael Glaesemann @ 2005-11-03 17:22 UTC (permalink / raw)
To: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>; +Cc: pgsql-sql
On Nov 4, 2005, at 1:17 , Judith Altamirano Figueroa wrote:
> Hi everybody, in Postgres 7.0.2 I have the next query:
>
> SELECT * from clientes_proceso where fecha_mod::date <= now() -1;
>
> but in version 8.0.1 returns the next error:
>
> ERROR: The operator doesn't exist: timestamp with time zone - integer
>
> How can drop a day to now()??
You can try a couple of different things: one is to use CURRENT_DATE
- 1 instead of now() -1. Another would be to cast now() to date,
e.g., now()::date - 1.
Hope this helps.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: date question
@ 2005-11-03 17:22 lucas@presserv.org
parent: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
1 sibling, 0 replies; 15+ messages in thread
From: lucas@presserv.org @ 2005-11-03 17:22 UTC (permalink / raw)
To: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>; +Cc: pgsql-sql
Quoting Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>:
> Hi everybody, in Postgres 7.0.2 I have the next query:
> SELECT * from clientes_proceso where fecha_mod::date <= now() -1;
> but in version 8.0.1 returns the next error:
> ERROR: The operator doesn't exist: timestamp with time zone - integer
> How can drop a day to now()??
Try using "now()::date", or "interval". Like:
select * from clientes_proceso where fecha_mod::date <= now()::date -1;
or:
select * from clientes_proceso where fecha_mod::date <= now() - '1
day'::interval;
---
Lucas
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-11-03 17:22 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-06 19:19 Date question Boulat Khakimov <boulat@inet-interactif.com>
2001-03-06 19:26 ` Michael Fork <mfork@toledolink.com>
2001-03-06 19:30 ` Francis Solomon <francis@stellison.co.uk>
2001-03-06 20:37 ` clayton cottingham <drfrog@smartt.com>
2001-03-06 19:40 ` Peter Eisentraut <peter_e@gmx.net>
2001-03-06 20:02 ` Jie Liang <jliang@ipinc.com>
2001-03-06 19:25 RE: Date question Mike Mascari <mascarm@mascari.com>
2003-06-13 11:44 date question javier garcia - CEBAS <rn001@cebas.csic.es>
2003-06-16 17:24 ` Re: date question Bruno Wolff III <bruno@wolff.to>
2003-06-17 18:09 ` Re: date question Peter Eisentraut <peter_e@gmx.net>
2003-06-20 17:33 ` Re: date question javier garcia - CEBAS <rn001@cebas.csic.es>
2003-06-22 11:35 ` Re: date question Bruno Wolff III <bruno@wolff.to>
2005-11-03 16:17 date question Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
2005-11-03 17:22 ` Re: date question Michael Glaesemann <grzm@myrealbox.com>
2005-11-03 17:22 ` Re: date question lucas@presserv.org
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