CODEWITHSUNDEEP
mysqlsqlrelational-database
NoIdea
NoIdea

Reputation: 69

Should I add an extra date column?

TL;DR: I have a column with a timestamp column. I want to filter by a particular date. Should I add a date coulmn?

id(AI/PK)    some    fields    timestamp    *date?*

So the query with only a timestamp colum would be something like

SELECT ? 
FROM [TABLE] 
WHERE YEAR(timestamp) = '2015' AND MONTH(timestamp) = '04' AND DAY(timestamp) = '1'

vs a query with an additional date column

SELECT ? FROM [TABLE] WHERE date = date('04-01-2015')?

I'm currently in a planning and design phase of my project. But it will be a very huge table in production. So the cons of having a date column are obviously the increased size of the table. I guess the con of not having a date column would be decreased performance. No?

Well I'm not sure if it's that easy.

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I don't think so. You can do:

SELECT ?
FROM [TABLE]
WHERE date(timestamp) = '2015-04-01'

Or, if you want the query to use an index:

SELECT ?
FROM [TABLE]
WHERE timestamp >= '2015-04-01' AND
      timestamp < date_add('2015-04-01', interval 1 day);

Upvotes: 1

Related Questions