Mysql Unix Timestamp Store?

Question

I got a bit of a problem here. My database stores unix timestamps as a string 2011-09-01 20:22:36 and I need it as a Unix Timestamp ########### so I can compare it using a > then symbol. But I also need to have it automatically set the timestamp on update (ON UPDATE CURRENT TIMESTAMP) as well as have a default of the timestamp which is not really that important cause I can do that in PHP if I need to.

How can I do this? timestamp is now a date/time combo string and not a integre so I cannot compare it?

My comparison string is

$sql = sprintf("SELECT nid, field_date_value, field_movie_location_value FROM content_type_mobile_event WHERE updated<'%s'", $vid);

Incase anyone is wondering.

Michael Berkowski · Accepted Answer · 2011-09-02 01:16:23Z

2

Use the UNIX_TIMESTAMP() function to convert it inside your query. If you must compare it to a Unix timestamp from PHP, it is easiest to allow MySQL to handle the column's conversion on its end.

$sql = sprintf("SELECT nid, othercols FROM content_type_mobile_event WHERE UNIX_TIMESTAMP(updated) < '%s'", $vid);

answered Sep 2, 2011 at 1:16

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vincent Savard · Accepted Answer · 2011-09-02 01:20:48Z

2

You can compare DATETIME columns with operators like > or <, so I don't see what the problem is. For example, you can do this :

SELECT *
  FROM table
 WHERE your_column > NOW() - INTERVAL 2 HOUR;

If you really need unix timestamps (you shouldn't, it's a bad habit PHP users have), you can use the function UNIX_TIMESTAMP :

SELECT UNIX_TIMESTAMP(your_col)
  FROM table;

You can also use FROM_UNIXTIME to convert a unix timestamp to a valid date :

SELECT *
  FROM table
 WHERE your_column > FROM_UNIXTIME($data)

This is what I would use if I really had to use a unix timestamp, but most of the time, you can do without.

edited Sep 2, 2011 at 1:20

answered Sep 2, 2011 at 1:15

Vincent Savard

36.1k10 gold badges71 silver badges73 bronze badges

2 Comments

Steven Over a year ago

Yes but NOW() is a mysql function to generate exactly what MySql is looking for then subtract 2 hours. I have a UNIX timestamp integre string that I need to compare to a string and it is not working.

Vincent Savard Over a year ago

Steven: You don't have to use NOW(), it was only an example. You can use dates in many ways, I suggest you to read the docs. I also edited my post to tell you about UNIX_TIMESTAMP and I'll make a second edit to talk about FROM_UNIXTIME.

Collectives™ on Stack Overflow

Mysql Unix Timestamp Store?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related