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
Creating System table with a X minutes increments
Moderator: NorbertKrupa
Re: Creating System table with a X minutes increments
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
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
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
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
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`
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.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Creating System table with a X minutes increments
Duje,
Maybe you can create a view like the following:
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:
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.
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);
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)
Hope this helps a little.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Creating System table with a X minutes increments
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:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Creating System table with a X minutes increments
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.
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.