How to find out the weekday from a date???

I modified that query to use the names in a table I have (date columns) and
the query worked fine... What version of PG are you using?

-Mitch

----- Original Message -----
From: "Jose Manuel Lorenzo Lopez" <jose-manuel.lorenzo-lopez@ica.conti.de>
To: "PGSQL" <pgsql-general@postgresql.org>
Sent: Tuesday, July 17, 2001 10:24 AM
Subject: [GENERAL] How to find out the weekday from a date???

Hello PG's,

I want to extract the weekday of a given 'date' type field in my table
when selecting. How can I do this?

For example in a table "bschft_days" filled with values with type 'date'

I want to select all the days within the month 'march' and output the

respective

weekday. I tryed this:

select extract(dow from timestamp(day)), day, userid from bschft_days

where date_part('month', day) = '3';

I am not sure how to do this in PostgreSQL, but if you are using PHP to
commincate with your database, you can use PHP's rich set of date
conversion function to do this and many other things.

Good Luck

-r

At 04:24 PM 7/17/01 +0200, Jose Manuel Lorenzo Lopez wrote:

I want to extract the weekday of a given 'date' type field in my table
when selecting. How can I do this?

...

select extract(dow from timestamp(day)), day, userid
from bschft_days where date_part('month', day) = '3';

...

ERROR: parser: parse error at or near "dow"

That seems to work on 7.1.x. Perhaps you are using an older version? In
either case, you can try putting single quotes around "dow", like

select extract('dow' from timestamp(day)), day, userid
from bschft_days where date_part('month', day) = '3';

hth

- Thomas

17.07.2001 16:50:33, "Mitch Vincent" <mvincent@cablespeed.com> wrote:

I modified that query to use the names in a table I have (date columns) and
the query worked fine... What version of PG are you using?

Hello Mitch,

I am using Postgresql 7.0.3!

17.07.2001 16:59:33, Thomas Lockhart <lockhart@fourpalms.org> wrote:

I want to extract the weekday of a given 'date' type field in my table
when selecting. How can I do this?

...

select extract(dow from timestamp(day)), day, userid
from bschft_days where date_part('month', day) = '3';

...

ERROR: parser: parse error at or near "dow"

That seems to work on 7.1.x. Perhaps you are using an older version? In
either case, you can try putting single quotes around "dow", like

select extract('dow' from timestamp(day)), day, userid
from bschft_days where date_part('month', day) = '3';

Hello Thomas,

I tried with the quotes but it doesn't work! :(

Is this a feature for PG 7.1.x only???

Best Regards / Mit freundlichen Gr��en / Un saludo

Jos� Manuel Lorenzo L�pez

why not use the date_part function again...

select date_part('dow',day), day, userid from bschft_days where
date_part('month', day) = '3';

hth
tamsin

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jose Manuel
Lorenzo Lopez
Sent: 17 July 2001 15:25
To: PGSQL
Subject: [GENERAL] How to find out the weekday from a date???

Hello PG's,

I want to extract the weekday of a given 'date' type field in my table
when selecting. How can I do this?

For example in a table "bschft_days" filled with values with type 'date'

I want to select all the days within the month 'march' and output the
respective
weekday. I tryed this:

select extract(dow from timestamp(day)), day, userid from bschft_days where
date_part('month', day) = '3';

But it only tells me:

ERROR: parser: parse error at or near "dow"

:(

Any ideas??

Thanks a lot in advance!

Best Regards / Mit freundlichen Gr��en / Un saludo

Jos� Manuel lorenzo L�pez

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Jose Manuel Lorenzo Lopez <jose-manuel.lorenzo-lopez@ica.conti.de> writes:

I am using Postgresql 7.0.3!

Support for 'dow' in extract() is new in PG 7.1. Time to update.

Or use the underlying date_part function:

play=> select date_part('dow', now());
date_part
-----------
2
(1 row)

seems to work fine in 7.0 as well as 7.1.

regards, tom lane