Doing a calculation in a query?

[Ali Akhtar <al...@gmail.com>]

I have a table for tracking orders. Each order has an `ordered_at` field
(can be a timestamp, or a long with the milliseconds of the timestamp) and
`shipped_at` field (ditto, timestamp or long).

orderd_at tracks when the order was made.

shipped_at tracks when the order was shipped.

When retrieving the orders, I need to calculate an additional field, called
'shipment_delay'. This is simply, 'shipped_at - ordered_at`. I.e how long
it took between when the order was made, and when it was shipped.

The tricky part is, that if an order isn't yet shipped, then it should just
return how many days it has been since the order was made.

E.g, if order was made on Jan 1 and shipped on Jan 5th, shipment_delay = 4
 days (in milliseconds if needed)

If order made on Jan 1, but not yet shipped, and today is Jan 10th, then
shipment_delay = 10 days.

I then need to sort the orders in the order of 'shipment_delay desc', i.e
show the orders which took the longest, at the top.

Is it possible to define 'shipment_delay' at the table or query level, so
it can be used in the 'order by' clause, or if this ordering will have to
be done myself after the data is received?

Thanks.

Re: Doing a calculation in a query?

[DuyHai Doan <do...@gmail.com>]

Assuming you're using Cassandra 3.0 or more, User Defined Functions (UDF)
can help you to compute the shipment_delay. For the ordering, since this
column is computed and not a clustering column, ordering won't be possible

More details about UDF: http://www.doanduyhai.com/blog/?p=1876

On Mon, Oct 10, 2016 at 6:08 PM, Ali Akhtar <al...@gmail.com> wrote:

> I have a table for tracking orders. Each order has an `ordered_at` field
> (can be a timestamp, or a long with the milliseconds of the timestamp) and
> `shipped_at` field (ditto, timestamp or long).
>
> orderd_at tracks when the order was made.
>
> shipped_at tracks when the order was shipped.
>
> When retrieving the orders, I need to calculate an additional field,
> called 'shipment_delay'. This is simply, 'shipped_at - ordered_at`. I.e how
> long it took between when the order was made, and when it was shipped.
>
> The tricky part is, that if an order isn't yet shipped, then it should
> just return how many days it has been since the order was made.
>
> E.g, if order was made on Jan 1 and shipped on Jan 5th, shipment_delay = 4
>  days (in milliseconds if needed)
>
> If order made on Jan 1, but not yet shipped, and today is Jan 10th, then
> shipment_delay = 10 days.
>
> I then need to sort the orders in the order of 'shipment_delay desc', i.e
> show the orders which took the longest, at the top.
>
> Is it possible to define 'shipment_delay' at the table or query level, so
> it can be used in the 'order by' clause, or if this ordering will have to
> be done myself after the data is received?
>
> Thanks.
>
>