Hi David, I've scoured the forum for an answer to this but can't find a solution...
As the title says, from my datafeeds I would like to import a date value from as a DATE rather than a VARCHAR(255) or similar
The only way I've successfully managed to import a date so far is using VARCHAR(255) - however I would like to be able to use this date to sort by, and with VARCHAR it doesn't work as I'd like
A further problem is that different datafeeds have the date listed in different ways, eg.
19/08/2009
19-08-2009
2009/08/19
2009-08-19T20:00:00
19th Aug 2009
Is it possible that at the admin/register stage you could tell PriceTapestry what format the date is in (to help, maybe using a dropdown menu?).. or perhaps use a bit of php to get the date into the same format for different datafeeds?
Thanks in advance for any advice you can offer
The datafeed that I was working on has the date in the format:
14/01/09
dd/mm/yy
(only 2 characters for the year)
Hi Jim,
I think the field value should be left to allow NULL; otherwise you will get 1970-01-01 (the Unix "epoch") wherever there is no date value - but we may need to come back to this as setting date='' in the SQL may cause the 1970 value anyway.
It sounds like the date format being dd/mm/yy is confusing strtotime; so if all dates are in that format (I think you said they are not, but perhaps worth trying this for starters - different rules may be required for different feeds) the following code as an alternative to the above should work:
if ($admin_importFeed["field_eventdate"])
{
$parts = explode("/",$record[$admin_importFeed["field_eventdate"]]);
$year = $parts[2];
$month = $parts[1];
$day = $parts[0];
$record[$admin_importFeed["field_eventdate"]] = $year."-".$month."-".$day;
}Cheers,
David.
Thanks David, that code works perfectly for the first datafeed - where every date is in the format (dd/mm/yyy) ie. 22/01/09
I have other feeds, which have the format:
datafeed2
22/01/2009
datafeed3
2009-01-22
datafeed4
22/01/2009 20:00:00
datafeed5
2009-01-22T20:00:00
(also how would I deal with a date seperated by a dot eg: 22.01.2009)
Would a drop-down selection box be possible in the admin area?
so the explode("/" can be changed for each feed.
Or is it better to hand code a seperate rule for each feed in the includes/admin.php ...
ie.
if datafeed1.. do this
if datafeed2 .. do this
Thanks for your help so far David, very much appreciated
Hi Jim,
The easiest way would be a separate rule for each merchant, which is straight forward to do. Handling all the above cases (just replace "datafeed1" etc. with the actual, registered merchant names), something like this should do the trick:
if ($admin_importFeed["field_eventdate"])
{
switch($admin_importFeed["merchant"])
{
case "datafeed1": // DD/MM/YY
case "datafeed2": // DD/MM/YYYY
case "datafeed4": // DD/MM/YYYY HH:MM:SS
$parts = explode("/",str_replace(" ","/",$record[$admin_importFeed["field_eventdate"]]));
$year = $parts[2];
$month = $parts[1];
$day = $parts[0];
break;
case "datafeed3": // YYYY-MM-DD
case "datafeed5": // YYYY-MM-DDTHH:MM:SS
$parts = explode("-",str_replace("T","-",$record[$admin_importFeed["field_eventdate"]]));
$year = $parts[0];
$month = $parts[1];
$day = $parts[2];
break;
}
$record[$admin_importFeed["field_eventdate"]] = $year."-".$month."-".$day;
}Notice how I have optimised the number of sections required by combining those that have the same separator and y/m/d order, just replacing the separator between the date and the time with the same character as separates the date parts...
Cheers,
David.
Hi Jim,
It is possible that PHP's strtotime() function might help here; which is able to parse almost any data representation (covering the different styles found in your feeds) into a standard Unix timestamp; which can then be converted into a date format suitable for inserting into MySQL.
I'm assuming that you have created the new field as per the instructions in node 313, so if you convert the field in the products table to DATE, you could use something like this within the import record handler function in includes/admin.php - just before constructing the main SQL statement to insert the product:
if ($admin_importFeed["field_date"]){
$date = strtotime($record[$admin_importFeed["field_date"]]);
$record[$admin_importFeed["field_date"]] = date("Ymd",$date);
}
Cheers,
David.