![]() And if both operands of a division are integers in SQL Server, the result is an integer division with type integer. In SQL Server, you will have to define the anchor timestamp, say ' 00:00:00', yourself, and use DATEDIFF() to calculate the number of seconds elapsed, and DATEADD() to add the custom-truncated seconds back. In Oracle, an integer division is achieved by wrapping the division expression into a FLOOR() function, and casting that to NUMBER(18,0), for example. TO_TIMESTAMP could also be TO_DATE, for example in Oracle, or a differently named function. +-Īnd, as they should, they also have a possibility to reverse that operation: SELECT , EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) AS unix_epoch_now ![]() It's the EXTRACT() function with the EPOCH keyword as the first parameter: SELECT That is the number of seconds elapsed since 1st January, 1970. Quite a lot of DBMS-s, in addition to Vertica, have a function call that returns the Unix epoch. Add the number of seconds obtained by that integer-division -> integer multiplication, to the same defined anchor timestamp.Multiply it back by the same number that you just integer-divided it by.Integer-divide that number of seconds, by the number of seconds that the wished interval of multiple of seconds, minutes, hours or days, consists of.Convert the timestamp to the number of seconds elapsed since a defined anchor timestamp.We can achieve this custom-truncation of a timestamp in several steps: In those cases, what you really need is the possibility of snapping a timestamp to the previous or next multiple of seconds, minutes, hours or days. every 3 hours? Or at a granularity that returns just exactly 1680 rows, as that is the number of pixels across supported by your display - out of the millions of data points of the two day's worth of data, several hundred data points per second, that someone presented you with? Or even adapting to the zoom level of the application displaying a graph? ![]() And you could group by that and get min/max/avg.īut what if what you really needed was one value 8 times a day - i.e. And all DBMS-s I know can truncate a timestamp value to the second, minute, hour or day, to "snap" a timestamp to the previous or next full unit of time. What could help you is a minimum/average/maximum value for a time slice. But millions of rows are nothing for human consumption - and far too much for plotting on a monitor that just has a few thousand pixels across - why fetch millions of rows across a busy network, when you can just plot a few thousand of them? You might have obtained a huge set of time stamped log data or sensor data that you would like to understand.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |