MySql unix_time() function with only date argument

Question

Ok, so the following works fine

mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');

But if I give only a date argument, like in:

mysql> SELECT UNIX_TIMESTAMP('2007-11-30');

Then somehow I am getting the timestamp equivalent to 2007-11-30 18:30 GMT. Can I somehow reset it to give timestamp for the beginning of that particular day? Like UNIX_TIMESTAMP('2007-11-30'); should give the timestamp equivalent of UNIX_TIMESTAMP('2007-11-30 00:00:00'); I need to filter out some records from a table based an event that happend after a certain date. Thanks

[EDIT]: I don't know how but this seems to be working as expected now. Screenshots: 2007-11:30 00:00:00 2007-11:30 18:30:00 2007-11:30

score 0 · Answer 1 · answered Aug 01 '12 at 12:30

I have checked it But i am getting same timestamp for '2007-11-30 00:00:00' and '2007-11-30'

mysql> SELECT UNIX_TIMESTAMP('2007-11-30 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2007-11-30 00:00:00') |
+---------------------------------------+
|                            1196361000 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2007-11-30');
+------------------------------+
| UNIX_TIMESTAMP('2007-11-30') |
+------------------------------+
|                   1196361000 |
+------------------------------+

Can you run these queries on your machine and check timestamp values.

hi, thanks for the response. I think this is related to the timezone of our system. Can you check the timezone of your msyql and add to the answer. Thanks. Help: [how to find the timezone of mysql](http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql) — roopunk, Aug 01 '12 at 18:07

score 0 · Answer 2 · answered Aug 01 '12 at 12:42

0

I also checked, for me as well it is giving for 00:00:00. On further investigation, I came across this Here:

The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 10.6, “MySQL Server Time Zone Support”.

On further searching, it became apparent that there is a variable system_time_zone, that is set when server starts using server machine's timezone. See here also

For each client connecting, they can set their own timezone as

mysql> SET time_zone = timezone;

So finally, you need to check your system_time_zone, set it to proper value.

I hope it will work well then....

answered Aug 01 '12 at 12:42

jsist

5,223
3
28
43

hi. thanks for the response. I think yes, this is related to the timezone of our system. Mine is in India UTC+5:30. And also 24:00 - 5:30 is 18:30 which is an interesting revelation I just came up with. Can you check with the time zone in which you checked the above queries and add to the response? Thanks – roopunk Aug 01 '12 at 18:12
Is your MySQL server running locally in India, or somewhere else? – jsist Aug 02 '12 at 05:02
yeah locally in india. ubuntu. – roopunk Aug 02 '12 at 15:52
will you provide the output value you are getting for "2007-11-30 10:30:19", "2007-11-30 00:00:00" and "2007-11-30" – jsist Aug 02 '12 at 16:54
dunno how but this seems to be working fine now. [one](http://imgur.com/ANgVA) [two](http://imgur.com/xCb5o) [three](http://imgur.com/Uljdg) closing the question – roopunk Aug 03 '12 at 09:56

score 0 · Answer 3 · answered Aug 06 '12 at 07:52

Found a way to make sure, that the UNIX_TIMESTAMP function returns the correct timestamp irrespective of anything interfering with the timezone etc

>> SELECT UNIX_TIMESTAMP(CONCAT(t.DATE,' 00:00:00')) FROM db.table_name t

That is, to append the 00:00:00 manually in the query string.

MySql unix_time() function with only date argument

3 Answers3