How to decode booking dates in Wordpress DB

Question

I ran this query in Wordpress DB and found out that Booking dates are stored in some coded way.

SELECT post_id, meta_id, post_title, meta_key, meta_value FROM a40443635734677.wp_7n2ncdd4yn_postmeta, a40443635734677.wp_7n2ncdd4yn_posts where post_id = ID and post_type = 'estate_property' and meta_key in ('property_price', 'cancellation', 'booking_dates', 'prop_featured', 'min_days_booking') and meta_key = 'booking_dates' and post_id = 248 order by post_id; Output: a:2:{i:1600646400;i:374;i:1600732800;i:374;}

From UI, it shows that booking dates are sept-21-2020 to sept-23-2020.

how to decode the data from DB (a:2:{i:1600646400;i:374;i:1600732800;i:374;}) to these dates?

Answer 1

The larger set of numbers you showed us look like seconds since the Epoch (January 1, 1970). You could use MySQL's FROM_UNIXTIME function convert these epoch values to a bona-fide human readable date. For example:

SELECT FROM_UNIXTIME(1600646400);  -- 21.09.2020 02:00:00

Answer 2

You can use unserialize() to convert it into an array.

$dbdata = 'a:2:{i:1600646400;i:374;i:1600732800;i:374;}';

$dbdata = unserialize($dbdata);

//result array ( 1600646400 => 374, 1600732800 => 374, )

Then use PHPs DateTime to convert to human readable format e.g.

$returnDate = date('d.m.Y H:i:s', 1600646400);

//result 21.09.2020 02:00:00

Answer 3

That is serialized data. Use PHPs built in unserialize( your output variable here ). You'll get an array since the serialized data is an array (a:2 means array with two indexes).

The date is in UNIX time. You can use PHPs DateTime to convert to human readable format.

$output = SELECT post_id, meta_id, post_title, meta_key, meta_value 
FROM a40443635734677.wp_7n2ncdd4yn_postmeta, a40443635734677.wp_7n2ncdd4yn_posts 
where post_id = ID 
and post_type = 'estate_property' 
and meta_key in ('property_price', 'cancellation', 'booking_dates', 'prop_featured', 'min_days_booking') and meta_key = 'booking_dates' 
and post_id = 248 order by post_id;

// Unserialize the data.
$unserialized = unserialize($output);

// Get the date for each array key. The date is the $key, so you pass that to the php date function.
foreach( $unserialized as $key => $date ) {
  echo date('M-d-Y', $key ) . "\n";
}

This will output Sept-20-2020 and Sept-21-2020 with the data you supplied.