pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtime series data
6+ messages / 2 participants
[nested] [flat]
* time series data
@ 2004-01-21 08:53 azwa@nc.com.my
0 siblings, 1 reply; 6+ messages in thread
From: azwa@nc.com.my @ 2004-01-21 08:53 UTC (permalink / raw)
To: pgsql-sql
Hi,
i want to create time table & the structure as below :
Column
Type
Modifiers
time_key
yr_id
month_id
month_desc
day_id
integer
integer
integer
text
integer
not null default nextval('time_seq'::text)
Example of data in time table:
Time_key
yr_id
month_id
month_desc
day_id
1999
1
Jan
1
1999
1
Jan
2
2000
1
Jan
1
time_key data will keep increment reading from sequence. i'm going to have
a data from yr_id =1994 --> 2009 , month_id =1 -->12
and day_id =1 -->31 for each month. So is there any solution (using
function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: time series data
@ 2004-01-21 09:37 Tomasz Myrta <jasiek@klaster.net>
parent: azwa@nc.com.my
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2004-01-21 09:37 UTC (permalink / raw)
To: azwa@nc.com.my; +Cc: pgsql-sql
Dnia 2004-01-21 09:53, Użytkownik azwa@nc.com.my napisał:
> time_key integer not null default nextval('time_seq'::text)
> Example of data in time table:
> Time_key yr_id month_id month_desc day_id
> 1999 1 Jan 1
> 1999 1 Jan 2
> 2000 1 Jan 1
Use this integer sequence and interval datatype to get date result:
your_date='1994-01-01'::date+'1 day'::integer * time_key
Now you can do whatever you want with this date - look at Postgresql
documentation "6.8. Date/Time Functions and Operators" -> "extract"
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: time series data
@ 2004-01-21 11:36 Tomasz Myrta <jasiek@klaster.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2004-01-21 11:36 UTC (permalink / raw)
To: ; +Cc: azwa@nc.com.my; pgsql-sql
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał:
> Use this integer sequence and interval datatype to get date result:
> your_date='1994-01-01'::date+'1 day'::integer * time_key
^^^^^^^
Sorry, use interval here.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: time series data
@ 2004-01-27 01:42 azwa@nc.com.my
0 siblings, 1 reply; 6+ messages in thread
From: azwa@nc.com.my @ 2004-01-27 01:42 UTC (permalink / raw)
To: pgsql-sql
Hi,
thanks for the info..btw can u pls explain a little bit detail since i
can't get thru yr solution.
thanks in advance
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał:
> Use this integer sequence and interval datatype to get date result:
> your_date='1994-01-01'::date+'1 day'::integer * time_key
^^^^^^^
Sorry, use interval here.
Regards,
Tomasz Myrta
azwa@postgresql.org
Sent by: pgsql-sql-owner@postgresql.org
01/21/2004 04:53 PM
To: pgsql-sql@postgresql.org
cc:
Subject: [SQL] time series data
Hi,
i want to create time table & the structure as below :
Column
Type
Modifiers
time_key
yr_id
month_id
month_desc
day_id
integer
integer
integer
text
integer
not null default nextval('time_seq'::text)
Example of data in time table:
Time_key
yr_id
month_id
month_desc
day_id
1999
1
Jan
1
1999
1
Jan
2
2000
1
Jan
1
time_key data will keep increment reading from sequence. i'm going to have
a data from yr_id =1994 --> 2009 , month_id =1 -->12
and day_id =1 -->31 for each month. So is there any solution (using
function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: time series data
@ 2004-01-27 09:56 Tomasz Myrta <jasiek@klaster.net>
parent: azwa@nc.com.my
0 siblings, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2004-01-27 09:56 UTC (permalink / raw)
To: azwa@nc.com.my; +Cc: pgsql-sql
Dnia 2004-01-27 02:42, Użytkownik azwa@nc.com.my napisał:
>
>
> Hi,
>
> thanks for the info..btw can u pls explain a little bit detail since
> i can't get thru yr solution.
> thanks in advance
insert into time_table (time_key,year_id,month_id,month_desc,day_id)
select
newid,
extract('year' from your_date),
extract('month' from your_date),
to_char(your_date,'mon'),
extract('day' from your_date)
...
from (select nextval('time_seq') as newid,
'1994-01-01'::date+'1 day'::interval * currval('time_seq') as your_date) x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: time series data
@ 2004-01-29 09:05 azwa@nc.com.my
0 siblings, 0 replies; 6+ messages in thread
From: azwa@nc.com.my @ 2004-01-29 09:05 UTC (permalink / raw)
To: pgsql-sql
Hi,
thanks for the feedback. btw i've run the statement below & got the
following result :
time_key | yr_id | month_id | month_desc | day_id
----------+-------+----------+------------+--------
193 | 1994 | 7 | jul |
13
(1 row)
actually if i'm going to have a result as below how could i did in my
statement ???
The result should appear as :
time_key | yr_id | month_id | month_desc |day_id
----------+-------+----------+-----------
1 | 1994 | 1 | Jan
2 | 1994 | 2 | Feb
3 | 1994 | 3 | Mac
4 | 1994 | 4 | Apr
5 | 1994 | 5 | May
6 | 1994 | 6 | Jun
7 | 1994 | 7 | July
8 | 1994 | 8 | Aug
9 | 1994 | 9 | Sept
10 | 1994 | 10 | Oct
11 | 1994 | 11 | Nov
12 | 1994 | 12 | Dec
.
.
.
the data for day_id should be incremental from 1->31 for each month for
specific year. meaning to say Every month should have the day_id
from 1---->31 . (follow exactly the day of the month)
Time_key
Yr_id
Month_id
Month_desc
Day_id(1-30/31 days)
1
1994
1
Jan
1
2
1994
1
Jan
2
3
1994
1
Jan
3
4
1994
1
Jan
4
5
1994
1
Jan
5
6
1994
1
Jan
6
7
1994
1
Jan
7
8
1994
1
Jan
8
9
1994
1
Jan
9
pls guide /help me to solve the above problem . thanks in advance.
Tomasz Myrta <jasiek@postgresql.org>
Sent by: pgsql-sql-owner@postgresql.org
01/27/2004 10:56 AM CET
To: azwa@nc.com.my
cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] time series data
Dnia 2004-01-27 02:42, Użytkownik azwa@nc.com.my napisał:
>
>
> Hi,
>
> thanks for the info..btw can u pls explain a little bit detail since
> i can't get thru yr solution.
> thanks in advance
insert into time_table (time_key,year_id,month_id,month_desc,day_id)
select
newid,
extract('year' from your_date),
extract('month' from your_date),
to_char(your_date,'mon'),
extract('day' from your_date)
...
from (select nextval('time_seq') as newid,
'1994-01-01'::date+'1 day'::interval * currval('time_seq') as your_date)
x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-29 09:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-21 08:53 time series data azwa@nc.com.my
2004-01-21 09:37 ` Tomasz Myrta <jasiek@klaster.net>
2004-01-21 11:36 ` Tomasz Myrta <jasiek@klaster.net>
2004-01-27 01:42 Re: time series data azwa@nc.com.my
2004-01-27 09:56 ` Tomasz Myrta <jasiek@klaster.net>
2004-01-29 09:05 Re: time series data azwa@nc.com.my
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