将DATE插入MYSQL数据库。将值从Android发布到PHP

I have a date value in format dd-mm-yyyy and I want to insert DATE or DATETIME value to mysql database, but the insert is always 0000-00-00

Already tried this but doesnt work for me.

$dateOne = $_POST['dateOne'];
$dateTwo = $_POST['dateTwo'];

$timestamp = date('Y-m-d', strtotime($dateOne));
$ts = date('Y-m-d', strtotime($dateTwo));

PHP part

$name = $_POST['name'];
$myname= $_POST['myName'];
$dateOne = $_POST['dateOne'];
$dateTwo = $_POST['dateTwo'];

$timestampa = date('Y-m-d', strtotime($dateOne));
$tsa = date('Y-m-d', strtotime($dateTwo));

$sql = "INSERT INTO hires_bp (suraduju_od, suraduju_do,  id_band, id_producer) VALUES ($timestampa, $tsa, (SELECT id FROM band WHERE band_name = '$myname'), (SELECT id FROM producer WHERE producer_name = '$name'))";

Android / Java part - for dateTwo.. dateOne is the same

mCurrentDate = java.util.Calendar.getInstance();
int year = mCurrentDate.get(java.util.Calendar.YEAR);
int month = mCurrentDate.get(java.util.Calendar.MONTH);
int day = mCurrentDate.get(java.util.Calendar.DAY_OF_MONTH);

DatePickerDialog mDatePicker = new DatePickerDialog(Calendar.this, new DatePickerDialog.OnDateSetListener() {
@Override
public void onDateSet(DatePicker view, int year, int month, int dayOfMonth) {
mDateTwoEditText.setText(dayOfMonth + "-" + (month+1) + "-" + year);
mCurrentDate.set(year, month, dayOfMonth);
}
}, year, month, day);
mDatePicker.show();
}

Getting strings from edittext and posting parameters.. using volley.. here's just a smal part of code cause I think there isn't a problem here.. other data inserts correct..

final String dateOne = mDateOneEditText.getText().toString().trim();
final String dateTwo = mDateOneEditText.getText().toString().trim();

......

protected Map<String, String> getParams() throws AuthFailureError {
                Map<String, String> params = new HashMap<>();
                params.put("name", name);
                params.put("myName", majName);
                params.put("dateOne", dateOne);
                params.put("dateTwo", dateTwo);

                return params;
            }

I expected the insert would be actual picked date, but instead the insert is 0000-00-00.. other data is inserted correctly..

MySql default date format is YYYY-MM-DD.

Change date format from dd-mm-yyyy to yyyy-mm-dd it will work

The problem was here:

$sql = "INSERT INTO hires_bp (suraduju_od, suraduju_do, id_band, id_producer) VALUES ($timestampa, $tsa, (SELECT id FROM band WHERE band_name = '$myname'), (SELECT id FROM producer WHERE producer_name = '$name'))";

'' missing --> $timestampa and $tsa..

'$timestampa' and '$tsa'

Smart objects, not dumb strings

We have data types in both Java and SQL, so use them. Thinking and working in mere strings is awkward, error-prone, and leaves you vulnerable to SQL-Injection attacks.

In contrast, working with appropriate data types makes your code simpler and more self-documenting, provides type-safety, and ensures valid values.

Avoid legacy date-time classes

You are using terrible date-time classes that were supplanted years ago by the java.time classes with the adoption of JSR 310.

java.time

Use LocalDate for a date-only value. This class would be appropriate for working with database columns of a type akin to the SQL-standard type DATE.

LocalDate ld = LocalDate.of( 2019 , 1 , 23 ) ;  // January 23, 2019.

Using JDBC 4.2 and later, pass to the database. Use a prepared statement to prevent SQL-Injection attacks.

myPreparedStatement.setObject( … , ld ) ;

Retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

Parse strings

The java.time classes use the standard ISO 8601 formats by default when parsing/generating strings that represent date-time values.

LocalDate ld = LocalDate.parse( "2019-01-23" ) ;

Generate strings

Generate strings for display to user. Either automatically localize with DateTimeFormatter.ofLocalizedDate, or generate a string in standard ISO 8601 format.

String output = ld.toString() ;  // Generate string in standard YYYY-MM-DD format.

To localize, specify:

  • FormatStyle to determine how long or abbreviated should the string be.
  • Locale to determine:
    • The human language for translation of name of day, name of month, and such.
    • The cultural norms deciding issues of abbreviation, capitalization, punctuation, separators, and such.

Example:

Locale l = Locale.CANADA_FRENCH ;   // Or Locale.US, Locale.JAPAN, etc.
DateTimeFormatter f = 
    DateTimeFormatter
    .ofLocalizedDate( FormatStyle.FULL )
    .withLocale( l )
;
String output = ld.format( f );

mercredi 23 janvier 2019

Table of date-time types in Java (both modern & legacy) and in standard SQL.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?