Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

TIME_SLICE

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

Answers

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

  • 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?

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

  • 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

  • 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.

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.