0

For example I use this to insert in table:

INSERT INTO `mytable` (`name`, `type`, `date`) VALUES ('Smit', 12, now());

Then I need get all or some rows from this table:

$st = $db->prepare("SELECT * FROM `mytable` WHERE id=:id");
$st->bindParam(':id', $id, PDO::PARAM_INT);
$st->execute();
$result = $st->fetchAll(PDO::FETCH_ASSOC);

Where I receive $result[0]['date'] as string = "2016-09-10 21:00:00". Maybe PDO has some PDO::PARAM_ which can set all date fields to timestamp?

P.S. Yes I know about php strtotime() which I can use here, but in this case we do double conversion. I mean, if I understood this right, MySQL save datetime as unix timestamp and when we get it as string, then convert again to a timestamp, and it is not a good solution.

cOle2
  • 4,725
  • 1
  • 24
  • 26
slava
  • 791
  • 1
  • 11
  • 26
  • 1
    See MySQL function [UNIX_TIMESTAMP](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_unix-timestamp). – drew010 Sep 25 '16 at 17:32
  • `select unix_timestamp(fieldname) as foo`? – Marc B Sep 26 '16 at 16:21
  • Thanks. Yes I know about this function. But in case using unix_timestamp(), i must change SQL syntax from `SELECT * ...` to `SELECT name type unix_timestamp(date) ...`. It is a solution, but in this case I'm tying myself to the table structure with the consequences. – slava Sep 26 '16 at 19:25

3 Answers3

2

MySQL does not store DATETIME as a UNIX timestamp.

MySQL's TIMESTAMP data type may be stored internally like a UNIX-style timestamp, but in spite of this, the default output is formatted like YYYY-MM-DD HH:MM:SS. You will have to convert it back.

You can format the MySQL datetime as a UNIX timestamp using an SQL function:

$st = $db->prepare("SELECT UNIX_TIMESTAMP(date) FROM `mytable` WHERE id=:id");
$st->bindParam(':id', $id, PDO::PARAM_INT);
$st->execute([$id]);
$result = $st->fetchAll(PDO::FETCH_ASSOC);

See http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_unix-timestamp

It would be worth your time to review the other functions on that page. There are a lot of things you can do in SQL expressions.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

Maybe PDO have some PDO::PARAM_ which can set all date fields to timestamp?

Nope, it doesn't.
There is no setting, neither in PHP, PDO or Mysql, that will tell mysql to return all dates as timestamps.

if i right understood, MySQL save datetime as unix timestamp

Nope, you understood it wrong.

and when we get it as string, then convert again in timestamp, it is not a good solution.

Don't worry about that. Just select your data in the format you need. As simple as that.
Do not optimize stuff that works all right. Do not solve problems that don't exist.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

P.S. Yes I know about php strtotime() which i can use here, but in this case we do double conversion. I mean, if i right understood, MySQL save datetime as unix timestamp and when we get it as string, then convert again in timestamp, it is not a good solution.

So if you want to avoid any conversions (manual or that under the hood) you shoud set up your field type in database as TIMESTAMP

https://dev.mysql.com/doc/refman/5.5/en/datetime.html

Patryk Uszyński
  • 1,069
  • 1
  • 12
  • 20