How to search profiles in database by creation date

Question

I have profiles with creation date column (DATETIME) in database. I need to implement search profiles by date in Java, which will return all profiles that were created on certain day or month.

What is the best way to implement this? I thought about adding new columns (day, month, year) and searching by them, but I don't know if this is the right way. Search shouldn't be slow and dates need to be localized.

Edit:

Yes, this query (from the other question) works:

SELECT * FROM profile WHERE DATE(creation_date) = '20190121';

What I see as a problem here is that this query will probably be slow in situation where there is a great number of profiles, because Date function must be performed first and than comparison on all creation dates. It seems to me that such solution is inefficient. I'm not sure if adding more columns (day, month and year as integers) will be a better solution, or maybe using indexing.

Possible duplicate of [How do I query for all dates greater than a certain date in SQL Server?](https://stackoverflow.com/questions/10643379/how-do-i-query-for-all-dates-greater-than-a-certain-date-in-sql-server) — , Jan 21 '19 at 10:17
Your problem is about the SQL query or how to use JAVA to get data from database? — Mickaël Leger, Jan 21 '19 at 10:29

score 0 · Answer 1 · answered Jan 21 '19 at 10:45

I thought about adding new columns (day, month, year) and searching by them, but I don't know if this is the right way. Search shouldn't be slow and dates need to be localized.

If you use a Datetime column that is triggered after a profile is insert in your database, you have all you need. You just need a column timezone maybe to know what is the timezone of the created profile.

You can do something like this :

// I have no idea how your column or table are named so here is an example
SELECT * FROM profile WHERE created_at = '?'

with ? the date your user is looking for.

Now for the localized part, here the doc : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz

SELECT * FROM profile WHERE CONVERT_TZ(created_at, 'GMT', profile_timezone) = '?'

where 'GMT' is your server timezone (replace by the real one) and profile_timezone the timezone of the profile.

This way when a user search a profile, you will return profile according to the datetime when it was created in the user timezone.

Is it what you are looking for?

score 0 · Answer 2 · answered Jan 21 '19 at 11:09

0

try this

Declare @startdate date ='1/1/2019'
Declare @Enddate date ='1/21/2019'

Select * from tablename where cast(creationdate as date) between @startdate and @Enddate

answered Jan 21 '19 at 11:09

Mohammad Shehroz

226
2
11

score 0 · Answer 3 · answered Jan 21 '19 at 12:50

Write the query as:

SELECT p.*
FROM profile p
WHERE p.creation_date >= 2019-01-21' AND
      p.creation_date < 2019-01-22' ;

Add an index on profile(creation_date), and the query should be fast -- and scalable (i.e. not slow down noticeably as the number of rows grows).

How to search profiles in database by creation date

3 Answers3