format date mysql

[frankz]

I have a field on a form with the date in the format dd / mm / yyyy (or dd-mm-yyyy),
 mysql use the yyyy-mm-dd format, how can I do the conversion?
for inserting and reading I would use the commands copyfrom and CopyTo.

[Anatol]

maybe you could just update the POST date before copy (untested):

$f3->set( 'POST.yourdate', implode('-', array_reverse(explode('-', $f3->get('POST.yourdate')))) );
$f3->get('user')->copyfrom('POST');

 (if it works it would probably be better to use DATE object to convert the format)

cheers,

– anatol

[Anatol]

Wait!

copyfrom takes a callback function:

$db = new DB\SQL('sqlite:db/ent.sqlite');
$f3->set('user',new DB\SQL\Mapper($db,'users'));
$f3->get('user')->copyfrom('POST',function($val) {
	// the 'POST' array is passed to our callback function
    return array_intersect_key($val, array_flip(array('name','age')));
});
$f3->get('user')->save();
});

https://fatfreeframework.com/3.6/sql-mapper#copyfrom

Means I would

• sanatize your user input as the documentation suggests
• if you cannot change the format form your datepicker (in your view) convert here the POST to be mysql conform

[bcosca]

I prefer/recommend storing date/time data in a more compact integer format (as returned by time()) and perform conversion of this internal representation in the view/template where you can format the date according to the user's language preferences and/or time zone settings. This way you save bytes and make your app more flexible.

[frankz]

it is possible to obtain an example of use of INT as the date for insert and read from DB?
I already have the data in the DB, èpossibile a conversion?

[Anatol]

Hi frankz,

I think what bong means with "int" is to use a timestamp returned by time()

Further to use this as storing format in your mysql DB:

create table foo(id INT, mytimestamp INT(11));

in PHP you have date() for converting a timestamp to a date:

echo date('m/d/Y', 1299446702);

and strtotime() to convert a date to timestamp:

strtotime('11-01-2017');

Bong, speaks about a conversion in your view. This means he suggests a conversation most likely with js.

I´ve created a little example for you which should give you the idea: https://jsfiddle.net/z43t8cu3/

You would get the "unixTimestamp" from the db f.e. as json response and convert it as date 

object to your needs.

Regarding your existing dates:

I think you could do a conversion with UNIX_TIMESTAMP() as described here:

http://stackoverflow.com/questions/14014641/convert-mysql-datetime-to-timestamp

Hope this helps,

cheers,

– anatol