TIME_SLICE

kfruchtman

Hi!
Can I use TIME_SLICE for days,months or years?

TIME_SLICE( expression, slice-length [, 'time‑unit' [, 'start‑or‑end' ] ] )

according to the documentation its only :HOUR
MINUTE
SECOND (default)
MILLISECOND
MICROSECOND

Vertica_Curtis

Time_slice is, by definition, a slice of time - meaning, from the hour down. If you want to aggregate stuff by day, month, or year, just use the functions day(), month(), and year().

kfruchtman

Thanks Curtis!
but If I want to run a query for a year and cut it to 12 months as a time series graph?
If is also possible with the year(),month() functions?

Vertica_Curtis

Yea, just group by year(), month().

kfruchtman

Hi Curtis!
Sorry I think am not sure about this.
Lets say I need to slice the time to years or months:if I don't have a time column sliced as I want and I want to take a chunk of time and slice it as the time_slice does? can it be done for months ,days or years?

Lets say I have my spins table and I want to slice the time as a time series query by months:
My SPINS TABLE:
id | time | bet | win | balance | deviceType | gameSessionId | playerId | currencyId | brandId | gameId
---------------+---------------------+-------+-------+----------+------------+---------------+-----------+------------+---------+--------
asi_XXXXXXXXX | 2021-03-02 15:01:56 | 37500 | 0 | 10236347 |
asi_YYYYYYYYY | 2021-03-02 15:01:57 | 37500 | 0 | 10198847 |
asi_ZZZZZZZZZ | 2021-03-02 15:01:58 | 37500 | 7500 | 10168847 |
asi_111111111 | 2021-03-02 15:02:00 | 37500 | 0 | 10131347 |

Ofcourse I have millions of rows... and I need to slice the time by months or by years ?

Thanks so much for answering
Keren

Vertica_Curtis

time_slice doesn't go up to days, months, or years. You can only slice "downwards" from the hour.

But if you want to aggregate on day, month, or year, there are helper functions that you can use.

select id, day(time), count(win), sum(balance) from spins group by 1,2 ; -- for example.

Day() is usually pretty vague, and just returns something like "20", so you often need to couple that with month, or year to make sense of it anyway. You could do this:

month(time) * 100 + day(time) So that you get something like "420" or "1129" or whatever - a bit more readable.

But you could also just use date() - that just returns the date portion of a timestamp. That's probably the easiest option here. You could also use date_part() or extract() - https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_PART.htm?tocpath=SQL Reference Manual|SQL Functions|Date/Time Functions|_____8

But those functions probably don't support things like 3-second intervals the way time_slice does.

kfruchtman

Thank you very much Curtis.
Helps me a lot!
Keren