Group by Date Only on a Datetime Column

Group by date only on a Datetime column

Cast the datetime to a date, then GROUP BY using this syntax:

SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);

Or you can GROUP BY the alias as @orlandu63 suggested:

SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;

Though I don't think it'll make any difference to performance, it is a little clearer.

How to group by DATE only in column datetime

Just move convert(varchar,d.log_date,101) into group by clause:

select
    u.FirstName + ' ' + u.LastName as [FullName],
    d.user_id,
    convert(varchar, d.log_date, 101) as log_date,
    min(d.login_time) as LOG_IN,
    max(d.logout_time) as LOG_OUT,
    sum(d.totaltime) as TOTHrs 
from tbldtr d
    inner join tblUsers u on d.user_id = u.User_Id
where d.user_id = 'ADMIN1' and d.log_date between '20130601' AND '20130615'
group by
     convert(varchar, d.log_date, 101),
     u.FirstName, u.LastName, d.user_id
order by log_date asc

Also, it's more safe to change dates in the where into unambiguous format - YYYYMMDD

How to group by date from datetime and order by date time

Remove the cast in the select():

SELECT MIN(Date)
FROM [TEST].[dbo].[PROD]
GROUP BY CAST(Date AS DATE)
ORDER BY MIN(Date);

By the way "Date" is a really bad name for a column that has a time component.

How can I group by date time column without taking time into consideration

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

using date of datetime in group by and order by in single SELECT versus using subquery

Try putting row_date into GROUP BY:

SELECT date(datetime_col) as row_date, count(*) as count
FROM table1
GROUP BY row_date
ORDER BY count DESC

Pandas: How to group by a datetime column, using only the time and discarding the date

Series.dt.time/DatetimeIndex.time returns the time as datetime.time. This isn't great because pandas works best withtimedelta64 and so your 'time' column is cast to object, losing all datetime functionality.

You can subtract off the normalized date to obtain the time as a timedelta so you can continue to use the datetime tools of pandas. You can floor this to group.

s = (df.drange - df.drange.dt.normalize()).dt.floor('5T')

df.groupby(s).mean()

---

                c0        c1
drange                      
00:00:00  0.436971  0.530201
00:05:00  0.441387  0.518831
00:10:00  0.465008  0.478130
...            ...       ...
23:45:00  0.523233  0.515991
23:50:00  0.468695  0.434240
23:55:00  0.569989  0.510291

---

Alternatively if you feel unsure of floor, this gets the identical output up to the index name

df['time'] = (df.drange - df.drange.dt.normalize())  # timedelta64[ns]
df.groupby(pd.Grouper(key='time', freq='5T')).mean()

Hive - Group by datetime column to date only

Use it both in the GROUP BY and the SELECT clauses.

cast (from_iso8601_timestamp(eventtime) as date) 

Python Pandas Group by date using datetime data

resample

df.resample('D', on='Date_Time').mean()

              B
Date_Time      
2001-10-01  4.5
2001-10-02  6.0

---

Grouper

As suggested by @JosephCottam

df.set_index('Date_Time').groupby(pd.Grouper(freq='D')).mean()

              B
Date_Time      
2001-10-01  4.5
2001-10-02  6.0

---

Deprecated uses of TimeGrouper

You can set the index to be 'Date_Time' and use pd.TimeGrouper

df.set_index('Date_Time').groupby(pd.TimeGrouper('D')).mean().dropna()

              B
Date_Time      
2001-10-01  4.5
2001-10-02  6.0

How can I group by dates only by month (without years)?

You can use .month to access months of the index:

out = ds.groupby(ds.index.month).mean()

Output:

      гдз по русскому языку 5 класс  гдз по английскому языку 5 класс   ...
date                                                                    
1                         27.200000                         28.733333   ...
2                         34.466667                         34.000000   ...
3                         26.200000                         27.000000   ...
4                         36.600000                         36.666667   ...
5                         20.133333                         20.866667   ...
6                          0.266667                          0.533333   ...
7                          0.066667                          0.266667   ...
8                          0.000000                          0.533333   ...
9                         33.400000                         30.733333   ...
10                        29.466667                         32.666667   ...
11                        31.000000                         31.800000   ...
12                        29.600000                         32.733333   ...


[12 rows x 69 columns]

---

Related Topics