3

i have a mysql trigger that updates a table when there's an insert in another table. My question is this, how can i convert the current date to bigint value, because it's the bigint value that needs to be updated. This is the update statement

UPDATE clocks SET last_clock_upload = NOW() WHERE clock_id = NEW.clock_id

How can i change the NOW() to bigint?

Liam Domingo
  • 263
  • 2
  • 6
  • 18

1 Answers1

5

Try this:

SELECT UNIX_TIMESTAMP(NOW( ) )
Stephan
  • 8,000
  • 3
  • 36
  • 42
  • 1
    This is not returning in the correct value. When I convert the datetime to ticks in C# I get 635110463830000000 but the UNIX_TIMESTAMP returns 1379413536 – Liam Domingo Sep 17 '13 at 11:13
  • UPDATE engagements SET activated_date = UNIX_TIMESTAMP(NOW()) - this is the update statment. The c# code: MysqlCommand cmd = new MysqlCommand("SELECT activated_date FROM engagements",con); MysqlDatareader dr = cmd.ExecuteReader(); dr.read(); Int64 act_date = dr.getInt64("activated_date"); Int64 now = DateTime.Now.Ticks; now and act_date are not the same. – Liam Domingo Sep 17 '13 at 11:25
  • @LiamDomingo why don't you make `activated_date` column to be `DATETIME` OR `TIMESTAMP` type? – Stephan Sep 17 '13 at 11:39
  • Would like to but cannot. – Liam Domingo Sep 17 '13 at 11:47
  • then check this question : http://stackoverflow.com/questions/249760/how-to-convert-unix-timestamp-to-datetime-and-vice-versa – Stephan Sep 17 '13 at 12:00
  • Found a way to do it. In the query just multiply the number of seconds by 10 000 000 to get the amount of ticks it is then add the amount off ticks of the epoch. – Liam Domingo Sep 18 '13 at 09:34
  • The timestamps in our database are in milliseconds. This MySQL function appears to show just seconds. Ergo, the result we need is: **(unix_timestamp(now()) * 1000)** -- The answer converts backe to "now", I'm happy. – will Mar 15 '16 at 23:31