Table Table there is a field Time, type is a datetime, save the data format is "2020-01-01 00:00:00", I want to group by data to the number of days is in the form of "yyyy - MM - dd" grouping,


The first method is to directly in the group by Time field use function for date formatting,



Another is another plus three fields, Year, Month, Day, to Year, Month, Day stored separately, then group by Year, Month, Day,


Which high efficiency?

CodePudding user response:

You can design table when the date and time as two separate fields

CodePudding user response:

If by date statistics, efficiency should be consistent, after all, the date when it is also a word data is stored in the actual value, if the monthly, or yearly statistics, then the individual fields than date field slightly tall, also not much higher than that one

CodePudding user response:

Are not much difference, does not need to be in this dispute,
Suggest or directly by date to group by.
If it is really slow, please post the actual SQL directly.

If you really want to care about, it is worthy of your reference below, once and for all:

 USE tempdb for 
GO 
CREATE TABLE (t 
Id INT IDENTITY (1, 1) PRIMARY KEY, 
[Time] DATETIME 
) 
GO 
INSERT INTO t VALUES (' 2020-09-01 23:15:00) 
INSERT INTO t VALUES (' 2020-09-02 22:15:00) 
INSERT INTO t VALUES (' 2020-09-02 23:15:00) 
GO 
-- -- -- -- -- -- -- -- -- above for testing table and test data 

- 1. Increase the computed columns 
The ALTER TABLE t ADD yyyyMMdd AS the CONVERT (CHAR (10), [Time], 120) PERSISTED. 
GO 
- (2) increase index 
The CREATE INDEX IX_t_yyyyMMdd ON t (yyyyMMdd); 
GO 

- 3. Summary query 
SELECT the yyyyMMdd, COUNT (1) AS the FROM CNT t GROUP BY yyyyMMdd 

CodePudding user response:

Is certainly group by column has been jumped over lesser,

Specifically how much less efficient, but also bad not how much,

CodePudding user response:

The first is more efficient