Round Date to 10 Minutes Interval

Round date to 10 minutes interval

select
  trunc(sysdate, 'mi')
  - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;

or even

select
  trunc(sysdate, 'mi')
  - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;

PHP DateTime round up to nearest 10 minutes

1) Set number of seconds to 0 if necessary (by rounding up to the nearest minute)

$second = $datetime->format("s");
if($second > 0)
    $datetime->add(new DateInterval("PT".(60-$second)."S"));

2) Get minute

$minute = $datetime->format("i");

3) Convert modulo 10

$minute = $minute % 10;

4) Count minutes to next 10-multiple minutes if necessary

if($minute != 0)
{
    // Count difference
    $diff = 10 - $minute;
    // Add difference
    $datetime->add(new DateInterval("PT".$diff."M"));
}

Edited, thanks @Ondrej Henek and @berturion

How to round down minute on datetime every 10 minutes

This seems like a non-answer answer, but I don't know how you could improve upon what you already have. It works, it's concise, it's flexible (not hard-coded to a specific interval), it truncates fractions of a minute, and it maintains the Kind property. What more do you need?

Test code:

static void Main(string[] args)
{
    TestRoundingForHourAfter(DateTime.Parse("10 AM"));
    TestRoundingForHourAfter(DateTime.Parse("10 AM").AddTicks(123456789));
}

static void TestRoundingForHourAfter(DateTime baseTime)
{
    foreach (DateTime input in Enumerable.Range(0, 60).Select(minutes => baseTime + TimeSpan.FromMinutes(minutes)))
    {
        DateTime output = RoundDown(input, TimeSpan.FromMinutes(10));

        Console.WriteLine($"{input:hh:mm:ss.fffffff} rounds to {output:hh:mm:ss.fffffff}");
    }
}

public static DateTime RoundDown(DateTime dt, TimeSpan d)
{
    var delta = dt.Ticks % d.Ticks;

    return new DateTime(dt.Ticks - delta, dt.Kind);
}

Test output:

10:00:00.0000000 rounds to 10:00:00.0000000
10:01:00.0000000 rounds to 10:00:00.0000000
10:02:00.0000000 rounds to 10:00:00.0000000
10:03:00.0000000 rounds to 10:00:00.0000000
10:04:00.0000000 rounds to 10:00:00.0000000
10:05:00.0000000 rounds to 10:00:00.0000000
10:06:00.0000000 rounds to 10:00:00.0000000
10:07:00.0000000 rounds to 10:00:00.0000000
10:08:00.0000000 rounds to 10:00:00.0000000
10:09:00.0000000 rounds to 10:00:00.0000000
10:10:00.0000000 rounds to 10:10:00.0000000
...
10:00:12.3456789 rounds to 10:00:00.0000000
10:01:12.3456789 rounds to 10:00:00.0000000
10:02:12.3456789 rounds to 10:00:00.0000000
10:03:12.3456789 rounds to 10:00:00.0000000
10:04:12.3456789 rounds to 10:00:00.0000000
10:05:12.3456789 rounds to 10:00:00.0000000
10:06:12.3456789 rounds to 10:00:00.0000000
10:07:12.3456789 rounds to 10:00:00.0000000
10:08:12.3456789 rounds to 10:00:00.0000000
10:09:12.3456789 rounds to 10:00:00.0000000
10:10:12.3456789 rounds to 10:10:00.0000000
...

Is there a way to round a timestamp up or down to the nearest 30 minute interval?

To round up - take processed date, add to it 30 minutes minus precision (1 minute in case of minutes, 1 second in case of seconds and so on), then take some "base" date ('2021-01-01' in case of my test data), find difference in minutes then divide by 30 and multiply by 30 and add the result back to the base date basically rounding down the modified date. Rounding down will work the same way without adding 30 minutes:

WITH dataset(time) AS (
   VALUES 
  (timestamp '2021-08-23 17:29:59'),
  (timestamp '2021-08-23 17:30:00'),
  (timestamp '2021-08-23 17:30:01'),
  (timestamp '2021-08-23 17:31:01'),
  (timestamp '2021-08-23 17:59:59'),
  (timestamp '2021-08-23 18:00:00'),
  (timestamp '2021-08-23 18:00:01')
 ) 
 
SELECT date_add(
  'minute', 
  date_diff(
    'minute',
    timestamp '2021-01-01', -- "base" date
    (time + interval '30' minute - interval '1' second) -- remove the +/- interval parts to round down
  ) / 30 * 30, 
  timestamp '2021-01-01') -- "base" date
FROM dataset

Output:

_col0
2021-08-23 17:30:00.000
2021-08-23 17:30:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:00:00.000
2021-08-23 18:30:00.000

How do I round a column of time data to the nearest 15 minutes in r?

Here an approach with lubridate. We first generate an arbitrary data set and then round it to 15minutes:

library(lubridate)
x <- seq(as.POSIXct("2021-05-19 10:00"), as.POSIXct("2021-05-19 11:00"), 240)
x

round_date(x, unit="15 mins")

Edit: here the same idea with minutes only. We use a fake date, append the time, round it to 15min and extract minutes only:

library(lubridate)
x <- c("0:03", "0:18", "0:33", "0:48")
format(round_date(as.POSIXct(paste("1900-01-01 ", x)), unit="15 mins"), "%M")

How to round timestamp to 10 minutes in Spark 3.0?

Convert the timestamp into seconds using unix_timestamp function, then perform the rounding by dividing by 600 (10 minutes), round the result of division and multiply by 600 again:

val df = Seq(
  ("2022-01-21 22:11:11"),
  ("2022-01-21 22:04:04"),
  ("2022-01-21 22:19:34"),
  ("2022-01-21 22:57:14")
).toDF("my_col").withColumn("my_col", to_timestamp($"my_col"))

df.withColumn(
  "my_col_rounded",
  from_unixtime(round(unix_timestamp($"my_col") / 600) * 600)
).show

//+-------------------+-------------------+
//|my_col             |my_col_rounded     |
//+-------------------+-------------------+
//|2022-01-21 22:11:11|2022-01-21 22:10:00|
//|2022-01-21 22:04:04|2022-01-21 22:00:00|
//|2022-01-21 22:19:34|2022-01-21 22:20:00|
//|2022-01-21 22:57:14|2022-01-21 23:00:00|
//+-------------------+-------------------+

You can also truncate the original timestamp to hours, get the minutes that your round to 10 and add them to truncated timestamp using interval:

df.withColumn(
  "my_col_rounded",
  date_trunc("hour", $"my_col") + format_string(
    "interval %s minute",
    expr("round(extract(MINUTE FROM my_col)/10.0)*10")
  ).cast("interval")
)

Linux: How to round off the date() to nearest interval

Would you please try the following:

mod=$(( 10#$(date +%M) \% 15 ))
date -d "-${mod} minutes" +%Y/%m/%d/%H/%M

The variable mod holds the remainder of the minutes divided by 15.
Then round down to the nearest 15 minute interval by subtracting mod.

[Edit]

The manpage of crontab says:

Percent-signs (%) in the command, unless
escaped with backslash (), will be changed into newline
characters, and all data after the first % will be sent to
the command as standard input.

If you want to execute the command within crontab, please modify the command as:

mod=$(( 10#$(date +\%M) \% 15 ))
date -d "-${mod} minutes" +\%Y/\%m/\%d/\%H/\%M

[Edit2]

If you want to embed the code in crontab file, please add a line which look like:

0 12 * * * username bash -c 'mod=$(( 10#$(date +\%M) \% 15 )); DATEVAR=$(date -d "-${mod} minutes" +\%Y/\%m/\%d/\%H/\%M); write.sh "$DATEVAR"'

Please modify the execution time/date and the username accordingly.
The default shell to execute crontab command may be /bin/sh. Then you will need to explicitly switch it to /bin/bash to execute bash commands.
My apology that a backslash in front of % 15 (modulo operation) was missing in my previous post.

How to round dates and times to dates and times in 45-minute intervals

EDIT

I misunderstood the question earlier. The desired output for updated data can be achieved by using some mathematical manipulation since datetime can be converted to numeric.

df$DateTime45 <- as.POSIXct(round(as.numeric(df$DateTime)/(45*60))*
                           (45*60),origin='1970-01-01', tz = 'UTC')

df
#             DateTime          DateTime45
#1 2016-08-23 00:22:23 2016-08-23 00:00:00
#2 2016-08-23 00:26:38 2016-08-23 00:45:00
#3 2016-08-23 01:04:12 2016-08-23 00:45:00
#4 2016-08-23 02:27:58 2016-08-23 02:15:00
#5 2016-08-23 03:04:31 2016-08-23 03:00:00
#6 2016-08-23 04:51:46 2016-08-23 04:30:00

Original Answer

In base R, one way would be to create a 45-minute interval and use cut/findInterval.

TimeIntervalLimits <- seq(as.POSIXct("2016-08-23 00:00:00", tz = 'UTC'), 
                    as.POSIXct("2016-08-24 00:45:00", tz = 'UTC'), by = "45 min")
df$DateTime45 <- cut(df$DateTime, TimeIntervalLimits)
#Or with `findInterval`
#df$DateTime45 <- TimeIntervalLimits[findInterval(df$DateTime, TimeIntervalLimits)]

df
#             DateTime          DateTime45
#1 2016-08-23 00:22:23 2016-08-23 00:00:00
#2 2016-08-23 01:04:12 2016-08-23 00:45:00
#3 2016-08-23 02:27:58 2016-08-23 02:15:00
#4 2016-08-23 03:04:31 2016-08-23 03:00:00
#5 2016-08-23 04:51:46 2016-08-23 04:30:00

As explained in the comments, cut takes breaks starting from minimum value in the vector. So one hack is to insert a fake timestamp in the vector from where we want to start the breaks and then use cut with breaks argument. This avoids creating TimeIntervalLimits vector.

df$DateTime45 <- cut(c(as.POSIXct('2016-08-23 00:00:00', tz = 'UTC'), 
                     df$DateTime), '45 mins')[-1]

Round Date to 10 Minutes Interval