Trimming minutes based on some condition

1

Hi Team,

I have a requirement where i want to change my Timestamp based on some condition:

Ex: Time stamp is : 2022-01-10 16:10:12.142 where minutes value is 10(if minutes value is <29 i want to change minute value as 0) so result should be:

Expected - 2022-01-10 16:00:00.000

And if timestamp value is : 2022-01-10 16:45:12.142 where minutes value is 45(if minutes value is >30 it should be 30) so expected output should be like
exp - 2022-01-10 16:30:00.000

please can any 1 help me that how can i achive in dremio.

Thanks in advance.

Thanks,
Saurabh

2

@sauku

Something like this?

SELECT TO_TIMESTAMP(UNIX_TIMESTAMP('2022-01-10 16:10:12.142', 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP('2022-01-10 16:10:12.142','YYYY-MM-DD HH24:MI:SS.FFF')%1800)
--2022-01-10 16:00:00.000

SELECT TO_TIMESTAMP(UNIX_TIMESTAMP('2022-01-10 16:45:12.142', 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP('2022-01-10 16:45:12.142','YYYY-MM-DD HH24:MI:SS.FFF')%1800);
--2022-01-10 16:30:00.000

General formula is: SELECT TO_TIMESTAMP(UNIX_TIMESTAMP(your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FFF') - UNIX_TIMESTAMP(your_timestamp_column,'YYYY-MM-DD HH24:MI:SS.FFF')%your_granularity_in_seconds)

3

Hi @lenoyjacob ,

thank you so much for your help.

Thanks,
Saurabh

4

Hi @lenoyjacob ,

Thanks for your response i needed 1 more help if you can try:

I have a table which has two column (date,txncount) i wanted to fina a batch count for a specific time.

batch count will be based on (how many 50k record are there in the specified dates):

Ex: below are the records in my table:

date 1 10k
date 2 20k
date3 20k
date 4 10k

in the above i can say 2 batch count from date1 to date4 (10k+20k+20k = 50k, 1 batch count, 10k will be in 2nd batch count);

can anyone help me on this that how i can get this output in dremio.

thanks for the help in advance.