pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcurrent_date / datetime stuff
10+ messages / 7 participants
[nested] [flat]
* current_date / datetime stuff
@ 2007-06-04 15:04 Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 16:25 ` Re: current_date / datetime stuff Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Joshua @ 2007-06-04 15:04 UTC (permalink / raw)
To: pgsql-sql
Hello,
I was hoping someone here may be able to help me out with this one:
Is there anything similiar to: SELECT current_date;
that will return the date of the first Monday of the month?
Please let me know.
Thanks,
Joshua
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
@ 2007-06-04 15:27 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 1 reply; 10+ messages in thread
From: Andrew Sullivan @ 2007-06-04 15:27 UTC (permalink / raw)
To: pgsql-sql
On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
> that will return the date of the first Monday of the month?
I guess you need to write a function to do this. I suppose you could
do it by finding out what day of the week it is and what the date is,
then counting backwards to the earliest possible Monday.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-06-04 15:59 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Glaesemann @ 2007-06-04 15:59 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>> that will return the date of the first Monday of the month?
>
> I guess you need to write a function to do this. I suppose you could
> do it by finding out what day of the week it is and what the date is,
> then counting backwards to the earliest possible Monday.
As Andrew said, there's no built-in function to do this, but it's
easy enough to write one. Here's a rough example (very lightly tested
and probably overly complicated)
CREATE OR REPLACE FUNCTION first_dow(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month +
CASE WHEN v_day_of_week <= $2 THEN $2 - v_day_of_week
ELSE 8 - v_day_of_week
END AS first_day_of_month
FROM (
SELECT v_first_day_of_month
, extract('dow' from v_first_day_of_month)::integer
AS v_day_of_week
FROM (SELECT date_trunc('month', $1)::date)
AS mon(v_first_day_of_month)) as calc;
$_$;
CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow($1, 1);
$_$;
select first_monday(current_date);
first_monday
--------------
2007-06-04
(1 row)
select first_monday('2007-04-01');
first_monday
--------------
2007-04-02
(1 row)
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-04 16:39 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Glaesemann @ 2007-06-04 16:39 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:
>
> On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
>
>> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>>> that will return the date of the first Monday of the month?
>>
>> I guess you need to write a function to do this. I suppose you could
>> do it by finding out what day of the week it is and what the date is,
>> then counting backwards to the earliest possible Monday.
>
> As Andrew said, there's no built-in function to do this, but it's
> easy enough to write one. Here's a rough example (very lightly
> tested and probably overly complicated)
And a little simpler:
CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
AS first_dow_of_month
FROM (
SELECT v_first_day_of_month
, extract('dow' from v_first_day_of_month)::integer
AS v_day_of_week
FROM (SELECT date_trunc('month', $1)::date)
AS mon(v_first_day_of_month)) as calc;
$_$;
CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow_of_month($1, 1);
$_$;
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-04 21:08 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-06-05 18:01 ` Re: current_date / datetime stuff Gerardo Herzig <gherzig@fmed.uba.ar>
2007-06-06 15:54 ` Re: current_date / datetime stuff Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
0 siblings, 2 replies; 10+ messages in thread
From: Kristo Kaiv @ 2007-06-04 21:08 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
>
> On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:
>
>>
>> On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
>>
>>> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>>>> that will return the date of the first Monday of the month?
>>>
>>> I guess you need to write a function to do this. I suppose you
>>> could
>>> do it by finding out what day of the week it is and what the date
>>> is,
>>> then counting backwards to the earliest possible Monday.
>>
>> As Andrew said, there's no built-in function to do this, but it's
>> easy enough to write one. Here's a rough example (very lightly
>> tested and probably overly complicated)
>
> And a little simpler:
>
> CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
> RETURNS DATE
> IMMUTABLE
> LANGUAGE SQL AS $_$
> SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
> AS first_dow_of_month
> FROM (
> SELECT v_first_day_of_month
> , extract('dow' from v_first_day_of_month)::integer
> AS v_day_of_week
> FROM (SELECT date_trunc('month', $1)::date)
> AS mon(v_first_day_of_month)) as calc;
> $_$;
>
> CREATE OR REPLACE FUNCTION first_monday(DATE)
> RETURNS DATE
> IMMUTABLE
> LANGUAGE SQL AS $_$
> SELECT first_dow_of_month($1, 1);
> $_$;
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
@ 2007-06-05 18:01 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-06-06 01:11 ` Re: current_date / datetime stuff Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Gerardo Herzig @ 2007-06-05 18:01 UTC (permalink / raw)
To: pgsql-sql
We should have a onliner contest. I love oneliners!!!
> oneliner:
>
> select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
> ('month',now()))||'days')::text)::interval;
>
> Kristo
> On 04.06.2007, at 19:39, Michael Glaesemann wrote:
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
2007-06-05 18:01 ` Re: current_date / datetime stuff Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2007-06-06 01:11 ` Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rodrigo De León @ 2007-06-06 01:11 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "Gerardo Herzig" <gherzig@fmed.uba.ar>
On 6/5/07, Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
> We should have a onliner contest. I love oneliners!!!
+1 on that
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
@ 2007-06-06 15:54 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
2007-06-06 15:56 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
1 sibling, 1 reply; 10+ messages in thread
From: Osvaldo Rosario Kussama @ 2007-06-06 15:54 UTC (permalink / raw)
To: Kristo Kaiv <kristo.kaiv@skype.net>; pgsql-sql
Kristo Kaiv escreveu:
> oneliner:
>
> select date_trunc('month',now()) + ((8 - extract('dow' from
> date_trunc('month',now()))||'days')::text)::interval;
>
There is a problem when first monday is 1st or 2nd day of month.
bdteste=# SELECT date_trunc('month',meses) + ((8 - extract('dow' from
date_trunc ('month',meses))||'days')::text)::interval FROM (SELECT
('2007-' || s.a || '-15')::timestamp as meses FROM generate_series(1,12)
as s(a)) AS foo;
?column?
---------------------
2007-01-08 00:00:00
2007-02-05 00:00:00
2007-03-05 00:00:00
2007-04-09 00:00:00
2007-05-07 00:00:00
2007-06-04 00:00:00
2007-07-09 00:00:00
2007-08-06 00:00:00
2007-09-03 00:00:00
2007-10-08 00:00:00
2007-11-05 00:00:00
2007-12-03 00:00:00
(12 registros)
Testing this condition we have the correct answer:
bdteste=# SELECT date_trunc('month',foo) + ((CASE WHEN extract('dow'
from date_trunc('month',foo)) > 1 THEN 8 ELSE 1 END) - extract('dow'
from date_trunc ('month',foo)))*'1 day'::interval FROM (SELECT ('2007-'
|| s.a || '-15')::timestamp as foo FROM generate_series(1,12) as s(a))
AS bar;
?column?
---------------------
2007-01-01 00:00:00
2007-02-05 00:00:00
2007-03-05 00:00:00
2007-04-02 00:00:00
2007-05-07 00:00:00
2007-06-04 00:00:00
2007-07-02 00:00:00
2007-08-06 00:00:00
2007-09-03 00:00:00
2007-10-01 00:00:00
2007-11-05 00:00:00
2007-12-03 00:00:00
(12 registros)
[]s
Osvaldo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Re: current_date / datetime stuff Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Re: current_date / datetime stuff Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Re: current_date / datetime stuff Kristo Kaiv <kristo.kaiv@skype.net>
2007-06-06 15:54 ` Re: current_date / datetime stuff Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
@ 2007-06-06 15:56 ` Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 0 replies; 10+ messages in thread
From: Kristo Kaiv @ 2007-06-06 15:56 UTC (permalink / raw)
To: Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>; +Cc: pgsql-sql
true, didn't test it that thoroughly: mod 7 should be bit more beautiful
select date_trunc('month',now()) + ( ((8 - extract('dow' from
date_trunc('month',now()))%7) ||'days')::text)::interval;
On 06.06.2007, at 18:54, Osvaldo Rosario Kussama wrote:
> Kristo Kaiv escreveu:
>> oneliner:
>> select date_trunc('month',now()) + ((8 - extract('dow' from
>> date_trunc('month',now()))||'days')::text)::interval;
>
>
>
> There is a problem when first monday is 1st or 2nd day of month.
>
> bdteste=# SELECT date_trunc('month',meses) + ((8 - extract('dow'
> from date_trunc ('month',meses))||'days')::text)::interval FROM
> (SELECT ('2007-' || s.a || '-15')::timestamp as meses FROM
> generate_series(1,12) as s(a)) AS foo;
> ?column?
> ---------------------
> 2007-01-08 00:00:00
> 2007-02-05 00:00:00
> 2007-03-05 00:00:00
> 2007-04-09 00:00:00
> 2007-05-07 00:00:00
> 2007-06-04 00:00:00
> 2007-07-09 00:00:00
> 2007-08-06 00:00:00
> 2007-09-03 00:00:00
> 2007-10-08 00:00:00
> 2007-11-05 00:00:00
> 2007-12-03 00:00:00
> (12 registros)
>
> Testing this condition we have the correct answer:
> bdteste=# SELECT date_trunc('month',foo) + ((CASE WHEN extract
> ('dow' from date_trunc('month',foo)) > 1 THEN 8 ELSE 1 END) -
> extract('dow' from date_trunc ('month',foo)))*'1 day'::interval
> FROM (SELECT ('2007-' || s.a || '-15')::timestamp as foo FROM
> generate_series(1,12) as s(a)) AS bar;
> ?column?
> ---------------------
> 2007-01-01 00:00:00
> 2007-02-05 00:00:00
> 2007-03-05 00:00:00
> 2007-04-02 00:00:00
> 2007-05-07 00:00:00
> 2007-06-04 00:00:00
> 2007-07-02 00:00:00
> 2007-08-06 00:00:00
> 2007-09-03 00:00:00
> 2007-10-01 00:00:00
> 2007-11-05 00:00:00
> 2007-12-03 00:00:00
> (12 registros)
>
> []s
> Osvaldo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: current_date / datetime stuff
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
@ 2007-06-04 16:25 ` Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Rodrigo De León @ 2007-06-04 16:25 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Joshua <joshua@joshuaneil.com>
On 6/4/07, Joshua <joshua@joshuaneil.com> wrote:
> Hello,
>
> I was hoping someone here may be able to help me out with this one:
>
> Is there anything similiar to: SELECT current_date;
> that will return the date of the first Monday of the month?
>
> Please let me know.
>
> Thanks,
> Joshua
select (
select
case i <= dow
when true then d + (i - dow + 7)
when false then d + (i - dow)
end
from (
select d
, extract(dow from d)::int as dow
, 1 as i -- monday
from (
select date_trunc('month',current_date)::date - 1 as d
) q
) q2
) as first_monday_of_the_month
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-06-06 15:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-04 15:04 current_date / datetime stuff Joshua <joshua@joshuaneil.com>
2007-06-04 15:27 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 15:59 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 16:39 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-04 21:08 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-06-05 18:01 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-06-06 01:11 ` Rodrigo De León <rdeleonp@gmail.com>
2007-06-06 15:54 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
2007-06-06 15:56 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-06-04 16:25 ` Rodrigo De León <rdeleonp@gmail.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