Page 1 of 2
Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 10:05 am
by Duje
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
Re: Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 10:09 am
by Rick
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 );
Re: Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 11:34 am
by Duje
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
Re: Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 11:53 am
by id10t
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`
Re: Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 7:38 pm
by JimKnicely
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.
Re: Creating System table with a X minutes increments
Posted: Thu Aug 09, 2012 8:23 pm
by JimKnicely
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);
Re: Creating System table with a X minutes increments
Posted: Fri Aug 10, 2012 2:00 pm
by Duje
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.