kfruchtmankfruchtman Vertica Customer

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
SECOND (default)


  • Options

    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().

  • Options
    kfruchtmankfruchtman Vertica Customer

    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?

  • Options

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

  • Options
    kfruchtmankfruchtman Vertica Customer

    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:
    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

  • Options

    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.

  • Options
    kfruchtmankfruchtman Vertica Customer

    Thank you very much Curtis.
    Helps me a lot!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file