Thread: 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
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:
>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
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
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!
> 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
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';
>
> 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
>
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