1

I have two tables, in both of them i have fields to store dates.
But in the first it stores the date as an integer and in the second like in time format.
I want to save the value from date format to integer, but it does not convert the date to int.
I tried the function idate(), but it converts only one char to parameter.
The date looks like string(19) "2006-08-09 13:22:44".
Can somebody help me? Maybe strtotime() this my solution?

Octfx
  • 691
  • 1
  • 7
  • 18
nowiko
  • 2,507
  • 6
  • 38
  • 82

4 Answers4

2

For PHP conversion use this:

$timestamp = strtotime($mysqltime);
echo date("Y-m-d H:i:s", $timestamp);

And for pure MySQL conversion:

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

Here, you can change the date string with your table field name (SELECT UNIX_TIMESTAMP(fieldName))

For reference:
https://stackoverflow.com/a/4577805/2883841 - the two answers with most votes.

Community
  • 1
  • 1
madcorp
  • 311
  • 2
  • 6
0

If your fieldtype is INT and you can't or don't want to change that to DATE or DATETIME, you should save the date as timestamp. In that case, strtotime is your function. Instead of converting by php strtotime() you could convert the fields directly in mysql:

UPDATE table1 SET `dateInt`=UNIX_TIMESTAMP(table2.`dateDate`) WHERE table1.`id`=table2.`id`
boulder_02
  • 301
  • 1
  • 6
0

Convert date from second table into Unix Time Stamp and save it into first table as int

EDIT

I think you should not do this conversion unless no other option left

N Kumar
  • 1,302
  • 1
  • 18
  • 25
0

Try UNIX_TIMESTAMP() e.g.

select UNIX_TIMESTAMP('2006-08-09 13:22:44')

1155126164
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51