pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zot O'Connor <zot@zotconsulting.com>
To: postgres sql <pgsql-sql@hub.org>
Subject: Re: [SQL] Week of year function?
Date: Mon, 25 Oct 1999 12:47:17 -0700
Message-ID: <3814B3C5.E9BAF9E5@zotconsulting.com> (raw)
References: <l03130302b43a3e85e018@[147.233.159.109]>
	<002501bf1f1b$c03a7da0$681b4cc0@public.ox.ac.uk>

Moray McConnachie wrote:

> > Seems you only need to divide the day of the year by seven to reach that,
> > don't you?
>
> I don't think that's quite right. You would need to add some maths to make
> sure that if January 1st is a Wednesday, week 1 of the year begins on
> January 6th (with Monday as first day of week) or Jan 5th (Sunday as first
> day of week).
> 

For my purposes Herouth's approach would work.  I came up with a much
more complicated function because I thought "day" of date_part was Day
of Month.  I wish the documentation would just give examples of each
value (just take one date and have a table of values).  

In fact the week of year is a but more complicated.  Intel for instance
started this work year on the last Sunday of December.  I can see this
being a company standards issue.

I merely wanted to group totals by week, so that I ducked the issue :)

Thanks!


Actually I now see I am partially correct.  A date_part of a datetime
does show the DoM:

Now|Mon Oct 25 12:24:47 1999 PDT
year|1999
month|10
day|25
hour|12
minute|24
second|47
decade|200
century|20
millenium|2
millisecond|0
microsecond|0
dow|1
epoch|940879487


But If I so a timespan:

select date_trunc('year','now'::datetime);
date_trunc                  
----------------------------
Fri Jan 01 00:00:00 1999 PST
(1 row)

=>  select 'now'::datetime - date_trunc('year','now'::datetime);
?column?                           
-----------------------------------
@ 297 days 11 hours 32 mins 54 secs
(1 row)

And run the same functions as above

year|0
month|0
day|297
hour|11
minute|29
second|45
decade|1
century|1
millenium|1
millisecond|0
microsecond|0
END 

I did not expect to see century/decade/millenium of 1 (which is fine
since it is consistent with itself), but this was not documented, and
should be.
Can be documented better?  I read everything to do with date and time
and search the mailing list for 4 hours and did not understand
date_trunc correctly.  Even cutting and pasting this note would help a
lot of people

My php code I used:

$array_names = array ("year", "month", "day", "hour", "minute",
"second", "decade", "century", "millenium", "millisecond", "microsecond"
);


while (list($key, $val) = each($array_names)) {
  $query="SELECT date_part('$val', 'now'::datetime - date_trunc('year',
'now'::datetime))";
  $fcs->query($query);
  $fcs->next_record();
  echo"$val|". $fcs->f("0") . "<BR>\n";
}


Thanks all!

-- 
Zot O'Connor

www.ZotConsulting.com
www.WhiteKnightHackers.com



view thread (7+ messages)  latest in thread

Message-ID: <3814B3C5.E9BAF9E5@zotconsulting.com>
Permalink:  ../3814B3C5.E9BAF9E5@zotconsulting.com/
Also on:    postgresql.org/message-id/3814B3C5.E9BAF9E5@zotconsulting.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: zot@zotconsulting.com, pgsql-sql@hub.org
  Subject: Re: [SQL] Week of year function?
  In-Reply-To: <3814B3C5.E9BAF9E5@zotconsulting.com>
  References: <l03130302b43a3e85e018@[147.233.159.109]> <002501bf1f1b$c03a7da0$681b4cc0@public.ox.ac.uk> <3814B3C5.E9BAF9E5@zotconsulting.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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