MySQL select timestamp col as seconds

Question

I wanna select a timestamp column that contain a date.

I want the result in seconds, I've tryed this but it's not working.

$req = $database->query('SELECT energy, maxEnergy, DATE_FORMAT(last_attack, "%s") FROM users WHERE id = '.$userID.'');

last_attack column contain : 2018-09-05 09:26:38

And the result give me : ["DATE_FORMAT(last_attack, "%s")"]=> string(2) "38"

I don't want the seconds in my date, I want all the date converted in seconds...

What am I doing wrong ? Thanks.

Possible duplicate of [Convert MySQL datetime to timestamp](https://stackoverflow.com/questions/14014641/convert-mysql-datetime-to-timestamp) — AymDev, Sep 05 '18 at 09:35
_I want the result in seconds_... seconds relative to what? UNIX epoch? Current time? — Salman A, Sep 05 '18 at 10:20

score 2 · Answer 1 · edited Sep 05 '18 at 10:46

2

You should use UNIX_TIMESTAMP

Select UNIX_TIMESTAMP(last_attack) as my_required from users...

edited Sep 05 '18 at 10:46

CKE

1,533
19
18
29

answered Sep 05 '18 at 10:28

Dilpazir Ahmad

31
3

score 0 · Answer 2 · answered Sep 05 '18 at 09:35

0

use TIME_TO_SEC

SELECT energy, maxEnergy, TIME_TO_SEC(last_attack) FROM users WHERE id = '.$userID.'

answered Sep 05 '18 at 09:35

Fahmi

37,315
5
22
31

score 0 · Accepted Answer · answered Sep 05 '18 at 09:59

Seems to me you are looking for the amount of seconds passed since the last_attack. You should use TIMESTAMPDIFF() to achieve that.

select TIMESTAMPDIFF(SECOND, '2018-09-05 09:26:38', NOW());

TIMESTAMPDIFF() accepts three parameters unit, date_time1 and date_time2 and returns the difference between date_time2 and date_time1 (date_time2 - date_time) in unit.

MySQL select timestamp col as seconds

3 Answers3