Converting a column to timestamp or datetime and querying the latest within a time interval

Question

I have a database dumped from csv where i have a column name time_stamp that I imported using VARCHAR

I have two things that I am confused about now,

1) I want to be able to convert this column to either timestamp or datetime its current format is in dd/mm/yyyy H:M:S but i read that unix accepts only - instead of /. Is this true?

2) I want to query from the database a timestamp value that is the highest within a certain time range using the column above

Example: between 7 AM and 8 AM , it should give me the timestamp which will be the closest to 8AM

Forgive me for my question if my question is vague, but i haven't been able to find much related to my question. I am sure that this is gonna receive some down votes so please be kind.

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

few things.. first look here to see more about the date format. Insert current date in datetime format mySQL

second you should probably update the table to change the / to a -.

use STR_TO_DATE() to convert

to get the most recent date then just do this

SELECT MIN(date) FROM table -- // MIN() is used to get the most recent timestamp
WHERE STR_TO_DATE(date, '%c/%e/%Y %r') BETWEEN $start AND $end

start and end would be your starting and ending dates

IF YOU ONLY WANT TO CHECK OFF OF THE TIME OF THE DAY USE THIS:

SELECT MIN(date) FROM table -- // MIN() is used to get the most recent timestamp
WHERE TIME(STR_TO_DATE(date, '%c/%e/%Y %r')) BETWEEN $start AND $end

per my previous recommendation you should update the table like this.

UPDATE table
SET date = STR_TO_DATE(date, '%c/%e/%Y %r')

something like that to fix it ... that will make your querys less complicated :)

edited May 23 '17 at 12:11

Community

1
1

answered May 19 '14 at 19:00

John Ruddell

24,127
5
51
83

I need to make it for for a certain time period, will this work for that as well ? – Bazinga777 May 19 '14 at 19:01
yes if you put the starting date as the start variable and ending date as the end variable – John Ruddell May 19 '14 at 19:02
sorry, my question may not have been correct. I want to query data from a certain time interval eg: 7 to 8 AM everyday and want to get data from a timestamp which will be closest to 8 AM, so can i query something like SELECT MIN(date) FROM table WHERE date BETWEEN 7:0:0 AND 7:59:59 – Bazinga777 May 19 '14 at 19:05
@Bazinga777 i just edited with the conversion on the date to compare. try it – John Ruddell May 19 '14 at 19:05
1

Oh so you don't care what day it happens on? just the hour? – John Ruddell May 19 '14 at 19:06
@Bazinga777 ok just edited to check off of time.. try that :) – John Ruddell May 19 '14 at 19:10
@Bazinga777 i added an update to my answer as well so you can change the values in the database as well.. that will make the query's much easier :) – John Ruddell May 19 '14 at 19:17
Thanks a lot, it did help me – Bazinga777 May 19 '14 at 20:49

score 0 · Answer 2 · edited May 19 '14 at 19:13

0

1) Yes, that is true. Date needs to be YYYY-mm-dd H:M:S. (you can convert it in php and then do an insert (date("Y-m-d H:i:s",strtotime($your_date))))

2)

SELECT * FROM table 
WHERE HOUR(date) BETWEEN 'start_date' AND 'end_date'
GROUP BY DAY(date)
ORDER BY DAY(date)

edited May 19 '14 at 19:13

John Ruddell

24,127
5
51
83

answered May 19 '14 at 19:01

Tanatos

1,817
1
12
12

You can also use: date('c', strtotime($your_date)); – pascalvgemert May 19 '14 at 19:02
I dont think the first option is a possibility as it would affect the performance of my application as I have about 10 million rows of data. Also, can startdate and end_date be replaced by a certain time period meaning any number of days but a certain time period say 7AM and 8AM everyday – Bazinga777 May 19 '14 at 19:03
sure, but you need HOUR(date) BETWEEN 'start_hour' AND 'end_hour' in that case – Tanatos May 19 '14 at 19:04
Ok, so i can just query hours instead of dates? is that what you mean ? – Bazinga777 May 19 '14 at 19:06
That's what you asked, isn't it ? – Tanatos May 19 '14 at 19:07
I believe you want to do MIN() instead of MAX() because dates are inverted... MAX() gives you the oldest date.. MIN() gives you the most recent :) – John Ruddell May 19 '14 at 19:14
he wants the Highest in a time range – Tanatos May 20 '14 at 05:20

Converting a column to timestamp or datetime and querying the latest within a time interval

2 Answers2