php - Weird date values SQL retrieval from the database with a BETWEEN operator

997

So i had something in mind with my search options in the database, but for some reason my uploaded dates are stored in my database in weird values (for example: 25-06-89 gets stored as 614728800). These weird values make it quite complicated for me to use them in a filter search where the user can retrieve documents in a 'between date'. The thing is, i am using a plugin for my wordpress site with which you can easily create database columns and use them in a upload form to populate the columns with data. One of these column options is the date field (in the plugin), though when i look at the database, this 'date field' is a column of type bigint(20) - probably so the user could insert multiple different date formats.

The date between(s) are fetched in a form where the user can fill in the from and to (with a datepicker) as far as i can see this returns a string value (though, could also be a date format with the datepicker? - don't know this for sure).

So i guess i have several options:

  1. Convert the string / date input form values to the weird values in the database and then do a SQL between on those weird values (no clue if this works though and how to do it).
  2. Edit the upload form and changing the date field type in the database to a timestamp/date (though, this will take a huge effort since it is a plugin and i don't want to rewrite the entire code).

So option 1 is preferred but the question is, how to do this? Perhaps there are also other options?

The code is as follows:

The HTML form for the from - to date

<form method="post">
    <label for="from">From</label>
    <input type="text" id="from" name="from">
    <label for="to">to</label>
    <input type="text" id="to" name="to">
<input type="submit" name="go" value="Submit"/>

The jQuery code for the datepicker show:

$(function() {
    $( "#from" ).datepicker({
      defaultDate: "+1w",
      dateFormat: "dd-mm-yy",
      changeMonth: true,
      numberOfMonths: 2,
      onClose: function( selectedDate ) {
        $( "#to" ).datepicker( "option", "minDate", selectedDate );
      }
    });
    $( "#to" ).datepicker({
      defaultDate: "+1w",
      dateFormat: "dd-mm-yy",
      changeMonth: true,
      numberOfMonths: 2,
      onClose: function( selectedDate ) {
        $( "#from" ).datepicker( "option", "maxDate", selectedDate );
      }
    });
  });

When i echo the $_POST[from] (or to) it returns the correct datepicked values (since it is input text i assume this is just a string?)

On the basis of the from - to form input i want to do a sql which retrieves values in the from - to date range. My idea was firstly as follows but because of the weird stored date values in my database this isn't possible anymore:

$criteria_from = $_REQUEST['from'];     
$criteria_to = $_REQUEST['to'];

     SELECT
            publication_date            
        FROM           
            wp_participants_database            
        WHERE 
            OrderDate BETWEEN #$criteria_from# AND #criteria_to#;

Thanks in advance!

946

Answer

Solution:

The 'weird' values that you speak of is the Unix time (or time since epoch) which is the number of seconds that have elapsed since January 1, 1970. The actual date and the weird value which you provided (25-06-89 gets stored as 614728800) correlate if you check on the Epoch Converter. So you need to see why the date is getting stored in the form of Unix time rather than a regular date datatype. Inspect your methods which store these values in the database to begin with.

This type of date representation might also be due to the fact that the date column in your database is of typeBIGINT(20). You should use theDATE datatype instead and the date value should appear normally.

People are also looking for solutions to the problem: php - AJAX not updating the list from MySQL

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.