Creating System table with a X minutes increments

Moderator: NorbertKrupa

Duje
Newbie
Newbie
Posts: 7
Joined: Thu Aug 09, 2012 9:51 am

Creating System table with a X minutes increments

Post by Duje » Thu Aug 09, 2012 10:05 am

Hello,

i'm new vertica. Correctly i'm resorting possibly to create a system table (table which will be automatically add new row) for time periods. time difference between records for example 30 min.
the general view if table like: date/time in 24H format; date/time in 12 H format; hours ; minute,...
so 2 records will be like:
2012-08-12 3:00:00 PM | 2012-08-12 15:00:00 | 2012 |08| 12 | 15 |00
2012-08-12 3:30:00 PM | 2012-08-12 15:30:00 | 2012 |08| 12 | 15 |30

is it possible to create such table?

thank you for advice

Rick
Newbie
Newbie
Posts: 23
Joined: Thu Jul 26, 2012 2:46 pm

Re: Creating System table with a X minutes increments

Post by Rick » Thu Aug 09, 2012 10:09 am

Hey and welcome to the forums.

Unsure of what you're actually asking, are you wanting to find a way to load data in intervals? If so won't this be up to the interfaces etc loading the data?

To get the difference between 2 dates/times you can use the DATEDIFF function if this is what you're asking

Code: Select all

DATEDIFF
Returns the difference between two date or time values, based on the specified start and end arguments.
Behavior Type
Immutable, except for TIMESTAMPTZ arguments where it is Stable.
Syntax 1
DATEDIFF ( datepart , startdate , enddate );
Syntax 2
DATEDIFF ( datepart , starttime , endtime );

Duje
Newbie
Newbie
Posts: 7
Joined: Thu Aug 09, 2012 9:51 am

Re: Creating System table with a X minutes increments

Post by Duje » Thu Aug 09, 2012 11:34 am

Hi Rick,

not exactly, I want to same a table (system or virtual) with will have date from last 2 years till future(e.g. 2 years).
and if last date < 1 year in table, Vertica will automatically create new rows for 2 year more.

and the second condition is - DATEDIFF should be 15 min between records.

i'm interesting - is it possible to create such table, which will automatically create records?

thank you

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Creating System table with a X minutes increments

Post by id10t » Thu Aug 09, 2012 11:53 am

Hi!

Vertica has no triggers or inner scheduler, so "table which will be automatically add new row" should be implemented by external scripts/procedures.
As option: you can write python daemon that each 30 mins will add a new row. If this option is acceptable by you I can help with python script.
As other option: use in `cron` or `at`
Last edited by id10t on Fri Aug 10, 2012 1:29 am, edited 1 time in total.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Creating System table with a X minutes increments

Post by JimKnicely » Thu Aug 09, 2012 7:38 pm

Duje,

Maybe you can create a view like the following:

Code: Select all

CREATE VIEW my_two_year_increment_vw AS
SELECT TO_CHAR(my_date, 'YYYY-MM-DD HH:MI:SS PM') my_date_12,
       TO_CHAR(my_date, 'YYYY-MM-DD HH24:MI:SS')  my_date_24,
       YEAR(my_date)                              my_date_year,
       MONTH(my_date)                             my_date_month,
       DAY(my_date)                               my_date_day,
       TO_NUMBER(TO_CHAR(my_date, 'HH'))          my_date_hour,
       MINUTE(my_date)                            my_date_min    
  FROM (SELECT TIMESTAMPADD(MINUTE, (ROW_NUMBER() OVER () - 1) * 30, ADD_MONTHS(TRUNC(SYSDATE), -24)) my_date
          FROM tables
         CROSS JOIN columns) foo
 WHERE foo.my_date <= ADD_MONTHS(TRUNC(SYSDATE), 24);
Then if you query the view, you'll always get timestamp data having 30 minute increments starting from SYSDATE - 24 months up to SYSDATE + 24 months...

For instance:

Code: Select all

dbadmin=> SELECT * FROM my_two_year_increment_vw;
       my_date_12       |     my_date_24      | my_date_year | my_date_month | my_date_day | my_date_hour | my_date_min
------------------------+---------------------+--------------+---------------+-------------+--------------+-------------
 2010-08-09 12:00:00 AM | 2010-08-09 00:00:00 |         2010 |             8 |           9 |           12 |           0
 2010-08-09 12:30:00 AM | 2010-08-09 00:30:00 |         2010 |             8 |           9 |           12 |          30
 2010-08-09 01:00:00 AM | 2010-08-09 01:00:00 |         2010 |             8 |           9 |            1 |           0
 2010-08-09 01:30:00 AM | 2010-08-09 01:30:00 |         2010 |             8 |           9 |            1 |          30
 2010-08-09 02:00:00 AM | 2010-08-09 02:00:00 |         2010 |             8 |           9 |            2 |           0
 2010-08-09 02:30:00 AM | 2010-08-09 02:30:00 |         2010 |             8 |           9 |            2 |          30
 2010-08-09 03:00:00 AM | 2010-08-09 03:00:00 |         2010 |             8 |           9 |            3 |           0
 2010-08-09 03:30:00 AM | 2010-08-09 03:30:00 |         2010 |             8 |           9 |            3 |          30
 2010-08-09 04:00:00 AM | 2010-08-09 04:00:00 |         2010 |             8 |           9 |            4 |           0
 2010-08-09 04:30:00 AM | 2010-08-09 04:30:00 |         2010 |             8 |           9 |            4 |          30
 2010-08-09 05:00:00 AM | 2010-08-09 05:00:00 |         2010 |             8 |           9 |            5 |           0
 2010-08-09 05:30:00 AM | 2010-08-09 05:30:00 |         2010 |             8 |           9 |            5 |          30
...
<< A lot more data >>
...
 2014-08-08 05:30:00 PM | 2014-08-08 17:30:00 |         2014 |             8 |           8 |            5 |          30
 2014-08-08 06:00:00 PM | 2014-08-08 18:00:00 |         2014 |             8 |           8 |            6 |           0
 2014-08-08 06:30:00 PM | 2014-08-08 18:30:00 |         2014 |             8 |           8 |            6 |          30
 2014-08-08 07:00:00 PM | 2014-08-08 19:00:00 |         2014 |             8 |           8 |            7 |           0
 2014-08-08 07:30:00 PM | 2014-08-08 19:30:00 |         2014 |             8 |           8 |            7 |          30
 2014-08-08 08:00:00 PM | 2014-08-08 20:00:00 |         2014 |             8 |           8 |            8 |           0
 2014-08-08 08:30:00 PM | 2014-08-08 20:30:00 |         2014 |             8 |           8 |            8 |          30
 2014-08-08 09:00:00 PM | 2014-08-08 21:00:00 |         2014 |             8 |           8 |            9 |           0
 2014-08-08 09:30:00 PM | 2014-08-08 21:30:00 |         2014 |             8 |           8 |            9 |          30
 2014-08-08 10:00:00 PM | 2014-08-08 22:00:00 |         2014 |             8 |           8 |           10 |           0
 2014-08-08 10:30:00 PM | 2014-08-08 22:30:00 |         2014 |             8 |           8 |           10 |          30
 2014-08-08 11:00:00 PM | 2014-08-08 23:00:00 |         2014 |             8 |           8 |           11 |           0
 2014-08-08 11:30:00 PM | 2014-08-08 23:30:00 |         2014 |             8 |           8 |           11 |          30
 2014-08-09 12:00:00 AM | 2014-08-09 00:00:00 |         2014 |             8 |           9 |           12 |           0
(70129 rows)
You'll have to mess with data types of the view's columns and the actual start and end dates per your requirements...

Hope this helps a little.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Creating System table with a X minutes increments

Post by JimKnicely » Thu Aug 09, 2012 8:23 pm

Duje,

I re-read the original request and it sounds like the start date of the data you want in the table is static. You can also do that with a view like the follow where I want my data to start on 01/01/2012:

Code: Select all

CREATE VIEW my_two_year_increment_vw AS
SELECT TO_CHAR(my_date, 'YYYY-MM-DD HH:MI:SS PM') my_date_12,
       TO_CHAR(my_date, 'YYYY-MM-DD HH24:MI:SS')  my_date_24,
       YEAR(my_date)                              my_date_year,
       MONTH(my_date)                             my_date_month,
       DAY(my_date)                               my_date_day,
       TO_NUMBER(TO_CHAR(my_date, 'HH'))          my_date_hour,
       MINUTE(my_date)                            my_date_min    
  FROM (SELECT TIMESTAMPADD(MINUTE, (ROW_NUMBER() OVER () - 1) * 30, DATE '2012-01-01') my_date
          FROM tables
         CROSS JOIN columns) foo
 WHERE foo.my_date <= ADD_MONTHS(TRUNC(SYSDATE), 24);
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Duje
Newbie
Newbie
Posts: 7
Joined: Thu Aug 09, 2012 9:51 am

Re: Creating System table with a X minutes increments

Post by Duje » Fri Aug 10, 2012 2:00 pm

Hi guys,
thank you for your help!

Jim, i'm interested in your answer.
could you please explain why do you add
FROM tables
CROSS JOIN columns
as I see I can use only FROm table which will give ne the same result.

Post Reply

Return to “Vertica Database Development”