SQL - Time since date

Question

I am struggling with one thing. I'm trying to calculate how many days ago a certain date was, by using SQL. The dates I have in my database can be in two formats:

Aug 28 2014, 17:17:34 CEST
Dec 29 2015, 01:03:14 CET

Those are two examples of different dates. Notice the "CET" and "CEST".

But anyways, how would I go ahead and calculate this in a SQL query? I managed to do this in PHP but I'd like to do this in the SQL query itself (if possible). Because it would save up on a lot of memory usage. I try make my work as fast as possible. I want to only access data from users that has only logged in the past 2-3 days or so. Of course I could make a SELECT * FROM users and then run PHP to check for the dates. But is there perhaps a way to do this in SQL? Like: SELECT * FROM users WHERE [lastlogin < 2 days]

Here is my current PHP code. I'd really want to do this in SQL. By the way, my columns are currently in text. Datetime does not work with that format for some reason.

$lastlogin = $row['lastlogin'];
$lastlogin = str_replace("\xc2\xa0",' ',$lastlogin);

$Date = $lastlogin;
$Date = substr($Date, 0, strpos($Date, " CE"));
$now  = date('Y-m-d');
$datetime1 = new DateTime($Date);
$datetime2 = new DateTime($now);
$interval = $datetime1->diff($datetime2);
$difference = $interval->format('%a days ago');

echo "Last login was: " . $difference;

http://stackoverflow.com/questions/4759248/difference-between-two-dates-in-mysql — Matt, Dec 29 '15 at 01:00
Yes but check my format. The format is the big issue here. The damn `CET` and `CEST` ruins it — Tibia Player, Dec 29 '15 at 01:03

score 2 · Accepted Answer · answered Dec 29 '15 at 04:29

you should alter your table to clean up the data. convert the data to two columns with the timezone info in one column and the date and time in another column. you can split the data easily using SUBSTRING_INDEX() and convert the string to datetime at the same time.

split on the "C" of "CET" and "CEST" like this:

SELECT SUBSTRING_INDEX("Aug 28 2014, 17:17:34 CEST","C",1)

and you will see you are left with the date and time part only, albeit still in a string format. That can be changed with STR_TO_DATE and you can do both on the fly.

First add the new columns to store the data:

ALTER yourtablename ADD newdatecolumn DATETIME AFTER oldcolumnname;
ALTER yourtablename ADD newtimezonecolumn VARCHAR(4) AFTER newdatecolumn;

UPDATE yourtablename
SET newdatecolumn = 
STR_TO_DATE(SUBSTRING_INDEX(olddatecolumn,"C",1), '%b %d %Y, %T')

you can then use SUBSTRING_INEX again, this time splitting on the last space in the column and grabbing the timezone for the other new column

UPDATE yourtablename
SET newtimezonecolumn = SUBSTRING_INDEX(olddatecolumn," ",-1)

then you will have data that you can work with more easily to use the suggested DATEDIFF() or other time and date functions. You can drop your old date column if you need to.

Note that yourtablename etc should be changed for actual table and column names.

Thanks a bunch! But how can I make all those queries as one? I need to do them in my PHP script, and not in the database. Because my database updates all the time and new entries are made. So I need to run all these queries, every time I run the script. Can I just copy all that and just separate them with a semicolon? Or would that bug anything? — Tibia Player, Dec 29 '15 at 14:47

score 0 · Answer 2 · answered Dec 29 '15 at 01:14

0

You can use to_days() or datediff() functions
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

answered Dec 29 '15 at 01:14

SQL - Time since date

2 Answers2