Tuesday 24 September 2019

MSSQL hours, groupy by day and hours, cast date vs date time, sub queries

extract hours

SELECT DATEPART(HOUR, GETDATE());

The DATEPART() function returns a specified part of a date.
https://www.w3schools.com/sql/func_sqlserver_datepart.asp
https://stackoverflow.com/questions/1114307/extracting-hours-from-a-datetime-sql-server-2005

// Records for everymonth in year
SELECT    COUNT(*)
FROM      table_emp
WHERE     YEAR(ARR_DATE) = '2012'
GROUP BY  MONTH(ARR_DATE)

https://stackoverflow.com/questions/9888263/count-records-for-every-month-in-a-year/9888386

// Count date time per hour

SELECT CAST(StartDate as date) AS ForDate,
       DATEPART(hour,StartDate) AS OnHour,
       COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

https://stackoverflow.com/questions/7001718/sql-server-group-by-count-of-datetime-per-hour

The CAST() function converts a value (of any type) into a specified datatype.
SELECT CAST('2017-08-25' AS date); //2017-08-25
SELECT CAST('2017-08-25' AS datetime); //2017-08-25 00:00:00.000

https://www.w3schools.com/sql/func_sqlserver_cast.asp

// Hour average
       SELECT      CAST(FLOOR(CAST(timestamp AS float)) AS datetime) AS day --strip time
            , DATEPART(hh, timestamp) AS hour
            , AVG(value) AS average
FROM        times
GROUP BY    CAST(FLOOR(CAST(timestamp AS float)) AS datetime)
            , DATEPART(hh, timestamp)

https://stackoverflow.com/questions/9744144/take-hourly-average-in-sql


// Group by date need to cast column into date
 GROUP BY CAST(column_date_a  AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' as DATE),  DATEPART(HOUR,  column_date_a AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time')

// Cast to a differnet timezone, requires cast to datetime
CAST(GETDATE() AS DATETIME) AT TIME ZONE 'Pacific Standard Time'

No comments:

Post a Comment