Rounding a Datetime Value Down to the Nearest Half Hour

Rounding a datetime value down to the nearest half hour

The answer by Ian is good, but it contains an unnecessary conversion. I suggest

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0,1)/48.0) FROM [tableName]

If you want to round to the nearest half-hour instead of always rounding down, use

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0)/48.0) FROM [tableName]

Rround DateTime to the closest half-hour

One method is the old DATEDIFF and DATEADD method:

SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,0,[DateTime])/30*30,0)

Round formatted time value to the nearest half-hour

Why don't you just do this:

$hour = date('H');
$minute = (date('i')>30)?'30':'00';
echo "$hour:$minute";

DEMO

UPDATE
I just realized that you asked all time to be rounded to :30:

$hour = date('H');
$minute = '30'; //always 30
echo "$hour:$minute";

round results of datediff (float) to nearest half hour

Here is one way you could achieve what you are after, which should give you enough to make a start on your problem:

declare @t table(s datetime, e datetime);
insert into @t values
 ('20170125 01:00:00','20170125 01:10:00'),('20170125 01:00:00','20170125 01:15:00'),('20170125 01:00:00','20170125 01:25:00'),('20170125 01:00:00','20170125 01:40:00'),('20170125 01:00:00','20170125 01:55:00'),('20170125 01:00:00','20170125 02:05:00'),('20170125 01:00:00','20170125 02:55:00'),('20170125 01:00:00','20170125 04:14:00'),('20170125 01:00:00','20170125 22:05:00');

select datediff(mi,s,e) as MinutesDiff

        ,datediff(mi,s,e) / 60 as HoursDiff
        ,datediff(mi,s,e) % 60 as MinutesRemain

        -- Take the Minutes Remaining after all the full hours are removed,
        -- and integer divide by 15 to get the number of full quarter hours
        -- and then round to the nearest number of minutes to add on.
        ,case round(datediff(mi,s,e) % 60 / 15,0)
            when 0 then 0
            when 1 then 30
            when 2 then 30
            when 3 then 60
            end as MinutesRounded
from @t;

Output:

+-------------+-----------+---------------+----------------+
| MinutesDiff | HoursDiff | MinutesRemain | MinutesRounded |
+-------------+-----------+---------------+----------------+
|          10 |         0 |            10 |              0 |
|          15 |         0 |            15 |             30 |
|          25 |         0 |            25 |             30 |
|          40 |         0 |            40 |             30 |
|          55 |         0 |            55 |             60 |
|          65 |         1 |             5 |              0 |
|         115 |         1 |            55 |             60 |
|         194 |         3 |            14 |              0 |
|        1265 |        21 |             5 |              0 |
+-------------+-----------+---------------+----------------+

Round Down Current_Timestamp to Nearest Half Hour in Snowflake/SQL

Transform to seconds from epoch, divide by 1800, round, and bring back to timestamp:

select to_timestamp(
  round(
    date_part(epoch_second, current_timestamp())/1800
  )*1800) nearest_half_hour  

# 2021-02-27T01:30:00Z               

Or any arbitrary timestamp:

select to_timestamp(
  round(
    date_part(epoch_second, to_timestamp('2020-10-10 17:51:01'))/1800
  )*1800) nearest_half_hour    

# 2020-10-10T18:00:00Z     

How to round unix timestamp up and down to nearest half hour?

Use modulo.

$prev = 1330518155 - (1330518155 % 1800);
$next = $prev + 1800;

The modulo operator gives the remainder part of division.

Rounding datetime to the nearest hour

You can round down with datetime.replace(), and round up by adding an hour to the rounded down value using datetime.timedelta(hours=1).

import datetime

def round_to_hour(dt):
    dt_start_of_hour = dt.replace(minute=0, second=0, microsecond=0)
    dt_half_hour = dt.replace(minute=30, second=0, microsecond=0)

    if dt >= dt_half_hour:
        # round up
        dt = dt_start_of_hour + datetime.timedelta(hours=1)
    else:
        # round down
        dt = dt_start_of_hour

    return dt

Note that since we're using replace the values we're not replacing (like the timezone - tzinfo) will be preserved.

How do I round datetime column to nearest quarter hour

Assuming that your series is made up of datetime objects, You need to use Series.apply . Example -

import datetime
df['<column>'] = df['<column>'].apply(lambda dt: datetime.datetime(dt.year, dt.month, dt.day, dt.hour,15*(dt.minute // 15)))

The above example to always round to the previous quarter hour (behavior similar to floor function).

EDIT

To round to the correct quarter hour (as in , if its 7 mins 30 seconds past previous quarter, to show the next quarter) . We can use the below example -

import datetime
df['<column>'] = df['<column>'].apply(lambda dt: datetime.datetime(dt.year, dt.month, dt.day, dt.hour,15*round((float(dt.minute) + float(dt.second)/60) / 15)))

The above would only take the latest seconds into consideration , if you want the millisecond/microsecond into consideration , you can add that to the above equation as - (float(dt.minute) + float(dt.second)/60 + float(dt.microsecond)/60000000)

Round time to nearest hour python

I experimented a bit with jpp but ended up with a different solution as adding one hour at hour 23 crashed the thing.

from datetime import datetime, timedelta

now = datetime.now()

def hour_rounder(t):
    # Rounds to nearest hour by adding a timedelta hour if minute >= 30
    return (t.replace(second=0, microsecond=0, minute=0, hour=t.hour)
               +timedelta(hours=t.minute//30))

print(now)
print(hour_rounder(now))

Returns:

2018-02-22 23:42:43.352133
2018-02-23 00:00:00

How to round time to the nearest quarter hour in Dart and Flutter?

You can do this in Dart and Flutter with an Extension on DateTime:

extension on DateTime{

  DateTime roundDown({Duration delta = const Duration(seconds: 15)}){
    return DateTime.fromMillisecondsSinceEpoch(
        this.millisecondsSinceEpoch -
        this.millisecondsSinceEpoch % delta.inMilliseconds
    );
  }
}

Usage:

DateTime roundedDateTime = DateTime.now().roundDown();
print(roundedDateTime);

Output: "2020-03-16 12:23:45.000"

or

DateTime roundedDateTime = DateTime.now().roundDown(delta: Duration(hour: 1));
print(roundedDateTime);

Output: "2020-03-16 12:00:00.000"

---

Related Topics