Pulling Our Pre-1970 Years from Unix Timestamp 1

[PCHomepage]

I realize that the MySQL functions relating to dates and the Unix timestamp are limited (for whatever reason) to positive numbers. Changing the field to a date-time field isn't an option. Right now I have:

SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(OpenDate), '%Y') AS YearAdded 
FROM tours 
WHERE DATE_FORMAT(FROM_UNIXTIME(OpenDate), '%Y') IS NOT NULL 
AND EventConfirmed = 1 
ORDER BY DATE_FORMAT(FROM_UNIXTIME(OpenDate), '%Y') ASC

which works but gives only two entries that are post-1970 even though there are many others in the table back to the '40s that are being ignored. Can anyone help with a workaround no matter how inelegant it may be?

 

[PCHomepage]

When I try that I get an error that time_zone is not a variable component but to be clear, this needs to show the same date and time for anyone anywhere in the world, not just for me here.

My actual full query is:

SELECT tours.ID, Event, EventType, Venue, City, StateName, YearOnly, OpenDate, CloseDate, Notes, CountryName AS CountryText, StateName AS StateText 
FROM (tours LEFT JOIN pchome_geoip.countries c ON c.ID = tours.Country) 
LEFT JOIN pchome_geoip.states s ON s.ID = tours.StateProvence 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) LIKE '%{Year}%')
ORDER BY OpenDate

which is working other than for the couple on different January 1st dates.

This query is the one being used for pulling up the list of years in the DHTML menu (not created using CodeCharge Studio) which also has 1945 and one or two others that are bogus:

SELECT YEAR(DATE_ADD(FROM_UNIXTIME(0), 
                            INTERVAL OpenDate SECOND)) AS YearAdded,
                            COUNT(OpenDate) as `NumEntries`
                            FROM tours 
                            WHERE EventConfirmed = 1 
                            GROUP BY YEAR(DATE_ADD(FROM_UNIXTIME(0), 
                            INTERVAL OpenDate SECOND))

I tried preceding these with another query, SET time_zone='UTC';, but it crashes.

 

[jpadie]

i don't know why it would 'crash'. it might give an 'unknown time zone' error or similar. but there is no reason for a 'crash' unless that is an artefact of whatever error handling method you use in codechargestudio.

the reference in the second code was incorrect. It should have been @@session.time_zone.

ELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND IF(
                '1955' != '', 
            (
             YEAR(
                CONVERT_TZ(
                    DATE_ADD(
                        FROM_UNIXTIME(0), 
                        INTERVAL OpenDate SECOND
                        ),
                    @@session.time_zone,
                    "+0:00"
                )
            ),
            '') = 1955

the first code snip works fine on each of the five mysql servers that i have in operation.

The better solution is the first as that will normalise the time zones between php and mysql. you should also expressly set the php time zone but you may already be doing that.

I don't understand why you repeat the requirement that the result must be agnostic to the location of the viewer. Why would it not be? does your server set its variables based on the browser location? or does it convert locale data at the server->browser level?

 

[PCHomepage]

Because it's a second query, CCS can't handle it so just gives a plain white screen which is its way of crashing. Of course, I test special queries like this outside of CCS first and yours above gives a syntax error at the last line in every one I tried.

Yes, PHP has the time zone set but CCS has no facility in its connection dialog for doing so, which is why it must be done within the query. I repeated the requirement just to be clear that we were on the "same page.

 

[jpadie]

you only need to send the timezone query once per connection.

when you say 'last line' I am confused. You mentioned the first query in your previous post. Which has only a few words and one line.

which query is giving you an error and what (precise mysql) error are you getting? that will help debug enormously.

By the way, I assume that it is evident that the last query I posted was missing the initial 'S' from 'SELECT'

 

[PCHomepage]

Yes, I noticed the missing S and fixed it before testing. Running the query said "syntax error on line 17" which was the last line of the query you last posted, presuming I counted them correctly. I am currently traveling with only my iPad but I can test queries on it against my live server using a MySQL app.

 

[jpadie]

My suggestion is to set the session variable and then query as normal (no need to use convert)

If there is a pressing reason why you cannot do this then I can try to debug the query further but this would involve dummying a table and data. Since this is the less good option I'd rather not spend the time in the absence of an overriding reason to avoid setting the timezone properly.

Of course someone else may have more time on their hands to debug the (simple) query.

 

[jpadie]

i had a few minutes this morning. this query does not produce errors

SELECT          * 
FROM            test2
WHERE           EventConfirmed =1
AND             IF(  '' !=  '', 
                    (YEAR(
                        CONVERT_TZ(
                            DATE_ADD( 
                                FROM_UNIXTIME( 0 ) , 
                                INTERVAL OpenDate SECOND ) ,
                            @@session.time_zone ,  
                            '+0:00' ) 
                        ) ),
                  ''
                ) =  ''

 

[PCHomepage]

No, it doesn't give any errors. However, if I plug in 1951 as the year, it still pulls up a 1952 entry that has no time. Of course, at the moment I'm testing on the iPad app and not on my PC so possibly there is some mismatch that way. Once I return late tonight, I'll test it properly.

 

[jpadie]

please provide the dataset row with the offending data.

 

[PCHomepage]

Applying your example to my full query, I came up with:

SELECT tours.ID, Event, EventType, Venue, City, StateName, YearOnly, OpenDate, CloseDate, Notes, CountryName AS CountryText, StateName AS StateText 
FROM (tours LEFT JOIN pchome_geoip.countries c ON c.ID = tours.Country) 
LEFT JOIN pchome_geoip.states s ON s.ID = tours.StateProvence 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
              CONVERT_TZ(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND) ,
                            @@session.time_zone ,  
                            '+0:00' )
                    )
            ) LIKE '%1951%'
ORDER BY OpenDate

However, it still brings up a 1952 date with the 1951 date. The "offending" row contains:

array( // row #1
		'ID' => 8388658,
		'Event' => '1952 Concert Series',
		'EventType' => 3,
		'Venue' => 'Various Venues',
		'City' => 'Various Cities',
		'StateName' => NULL,
		'YearOnly' => 1,
		'OpenDate' => -568080000,
		'CloseDate' => NULL,
		'Notes' => 'Introduction to the United Kingdom concert series. Specific shows are listed below.',
		'CountryText' => 'United Kingdom',
		'StateText' => NULL,
	),
);

When I convert the OpenDate value using one of the online converters (

http://www.epochconverter.com)

it shows properly as:

GMT: Tue, 01 Jan 1952 00:00:00 GMT
Your time zone: Monday, December 31, 1951 4:00:00 PM GMT-8

 

[jpadie]

There was a gotcha in the mysql manual.

i ran several 'unit'-style tests from php using the following script

<?php
$host = '127.0.0.1';
$port = '8889';
$user = 'root';
$pwd = 'root';

try{
  $pdo = new PDO("mysql:host={$host};port={$port}",$user,$pwd);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  die($e->getMessage());
}

$sql[] = "create database if not exists datetest";
$sql[] = "use datetest";
$sql[] = 'create table if not exists test (openDate int(10))';
$sql[] = 'delete from test';
$sql[] = 'insert into test (openDate) values (-568080000)';

foreach($sql as $query):
  try{
    $pdo->exec($query);
  } catch (PDOException $e) {
    echo $e->getMessage();
    die;
  }
endforeach;

echo "++++++++++++++++\n";
echo 'PHP Tests' . "\n";
$timestamp = -568080000;
$pst = new datetimezone('PST');
$utc = new datetimezone('UTC');
//$base = new datetime($timestamp, $pst);
$year = 1952;
foreach(array($pst,$utc) as $timezone):
  $inferredTimeStamp = new datetime($year . '-01-01', $timezone);
  echo $inferredTimeStamp->getTimeStamp();
  echo "\t" . $timezone->getName();
  echo "\n";
endforeach;

echo "++++++++++++++++\n";
echo "Mysql tests \n";


function getMySqlOffset(){
  global $pdo;
  $sql = "select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) as output";
  $s = $pdo->query($sql);
  $row = $s->fetchObject();
  list($h, $m, $s) = explode(":", $row->output);
  if(substr($h,0,1) != '-') $h = '+'.$h;
  $mysqlOffset = "$h:$m";
  return $mysqlOffset;
}
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";

echo "Force emulation of PC Homepage\n";
$pdo->exec("SET @@session.time_zone='-08:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";

echo "Mysql date retrieval tests\n";
$sql = "select openDate as ts, from_unixtime(openDate) as df from test";
echo "Should show NULL on df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}

$sql = <<<SQL
SELECT          @@session.time_zone as current_time_zone,
        openDate as ts,
        DATE_ADD(
                                FROM_UNIXTIME( 0 ) ,
                                INTERVAL OpenDate SECOND
                        ) as df
FROM            test
SQL;

echo "Should show a 1951 date for df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}

$sql = <<<SQL
SELECT          @@session.time_zone as current_time_zone,
        openDate as ts,
        CONVERT_TZ(
          DATE_ADD(
                                FROM_UNIXTIME( 0 ),
                                INTERVAL OpenDate SECOND
                        ),
                    @@session.time_zone,"+0:00"
                    ) as df
FROM            test
SQL;

echo "Should show a 1952 date for df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}
echo "But we don't.... \nWhy ...\n";
echo "From the manual: \n";
echo "If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs.\n";
echo "So mysql is implicitly converting to timestamps internally, and as we are dealing with early dates, the process is failing\n";
echo "So we need a different approach\n";




$sql = <<<SQL
SELECT          @@session.time_zone as current_time_zone,
        openDate as ts,
        DATE_SUB(
          DATE_ADD(
                             FROM_UNIXTIME( 0 ),
                             INTERVAL OpenDate SECOND
                    ),
                    INTERVAL @@session.time_zone HOUR_MINUTE) as df
FROM            test
SQL;

echo "Should show a 1952 date for df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}
echo "And we do .... \n\n";

echo "Now check whether this works with positive offsets too \n";
echo "Setting time zone to +5:00\n";
$pdo->exec("SET @@session.time_zone='+05:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";


$sql = <<<SQL
SELECT          @@session.time_zone as current_time_zone,
        openDate as ts,
        DATE_SUB(
          DATE_ADD(
                             FROM_UNIXTIME( 0 ),
                             INTERVAL OpenDate SECOND
                    ),
                    INTERVAL @@session.time_zone HOUR_MINUTE) as df
FROM            test
SQL;

echo "Should show a midnight 1952 date for df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}
echo "And we do .... \n\n";
echo "So that works ...\n";

echo "Now let's try a normal approach, by setting the session time zone properly first before querying;";
echo "Setting time zone to +0:00\n";
$pdo->exec("SET @@session.time_zone='+00:00'");
$mysqlOffset = getMysqlOffset();
echo "current mysql timezone is $mysqlOffset\n";

$sql = <<<SQL
SELECT          @@session.time_zone as current_time_zone,
        openDate as ts,
          DATE_ADD(
                             FROM_UNIXTIME( 0 ),
                             INTERVAL OpenDate SECOND
                    )
                as df
FROM            test
SQL;

echo "Should show a midnight 1952 date for df \n";
try{
  $s=$pdo->query($sql);
  $row = $s->fetchObject();
  print_r($row);
  echo "\n";
} catch(PDOException $e){
  echo $e->getMessage();
}

echo "And we do.\n\n";

echo "FAOD - trying the full(ish) PC Homepage query with variable at 1951, 1952 and '' \n";

$sql = <<<SQL
SELECT          @@session.time_zone,
        year(DATE_ADD(
                             FROM_UNIXTIME( 0 ),
                             INTERVAL OpenDate SECOND
                )) as df
FROM            test
WHERE
              IF(  '%s' !=  '',
                    YEAR(
            DATE_ADD(
                            FROM_UNIXTIME( 0 ) ,
                            INTERVAL OpenDate SECOND
                            )
                        ),
                    ''
                    ) = '%s'
SQL;

try{
  
  foreach(array('1951','1952','') as $var):
    echo "Setting variable  =  ";
    echo $var == '' ? "Empty string\n" : $var . "\n";
    $query = sprintf($sql, $var, $var);
    $s = $pdo->query($query);
    $row = $s->fetchAll(PDO::FETCH_OBJ);
    if(count($row) == 0):
      echo "No results \n\n";
    else:
      print_r($row);
      echo "\n";
    endif;
  endforeach;
} catch (PDOException $e) {
  echo 'error';
  echo $e->getMessage();
}
?>

the output I got was useful

++++++++++++++++
PHP Tests
-568051200  America/Los_Angeles
-568080000  UTC
++++++++++++++++
Mysql tests 
current mysql timezone is +01:00
Force emulation of PC Homepage
current mysql timezone is -08:00
Mysql date retrieval tests
Should show NULL on df 
stdClass Object
(
    [ts] => -568080000
    [df] => 
)

Should show a 1951 date for df 
stdClass Object
(
    [current_time_zone] => -08:00
    [ts] => -568080000
    [df] => 1951-12-31 16:00:00
)

Should show a 1952 date for df 
stdClass Object
(
    [current_time_zone] => -08:00
    [ts] => -568080000
    [df] => 1951-12-31 16:00:00
)

But we don't.... 
Why ...
From the manual: 
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs.
So mysql is implicitly converting to timestamps internally, and as we are dealing with early dates, the process is failing
So we need a different approach
Should show a 1952 date for df 
stdClass Object
(
    [current_time_zone] => -08:00
    [ts] => -568080000
    [df] => 1952-01-01 00:00:00
)

And we do .... 

Now check whether this works with positive offsets too 
Setting time zone to +5:00
current mysql timezone is +05:00
Should show a midnight 1952 date for df 
stdClass Object
(
    [current_time_zone] => +05:00
    [ts] => -568080000
    [df] => 1952-01-01 00:00:00
)

And we do .... 

So that works ...
Now let's try a normal approach, by setting the session time zone properly first before querying;Setting time zone to +0:00
current mysql timezone is +00:00
Should show a midnight 1952 date for df 
stdClass Object
(
    [current_time_zone] => +00:00
    [ts] => -568080000
    [df] => 1952-01-01 00:00:00
)

And we do.

FAOD - trying the full(ish) PC Homepage query with variable at 1951, 1952 and '' 
Setting variable  =  1951
No results 

Setting variable  =  1952
Array
(
    [0] => stdClass Object
        (
            [@@session.time_zone] => +00:00
            [df] => 1952
        )

)

Setting variable  =  Empty string
Array
(
    [0] => stdClass Object
        (
            [@@session.time_zone] => +00:00
            [df] => 1952
        )

)

the moral of this story is that if people are doing date stuff in mysql and php they must think about normalising the timezones before doing anything. This code will normalise the time zones. You should run this just after creating your database connection

function normaliseTimeZones(){
    $baseTimeZone = 'UTC';
    date_default_timezone_set($baseTimeZone);
    $tz = new dateTimeZone($baseTimeZone);
    $t = new datetime('', $tz);
    $offset = sprintf('%+03d:%02u', $t->getOffset() / 3600, abs($t->getOffset()) % 3600 / 60);
    mysql_query("SET @@session.time_zone = '$offset'");
}

but if you insist on doing things the hard way then you can use on the fly timezone conversion (to UTC) with this query

DATE_SUB(
    DATE_ADD(
        FROM_UNIXTIME( 0 ),
        INTERVAL OpenDate SECOND
    ),
    INTERVAL @@session.time_zone HOUR_MINUTE
)

if you want to convert to any other timezone then you would cascade the functions

DATE_ADD(
    DATE_SUB(
        DATE_ADD(
            FROM_UNIXTIME( 0 ),
            INTERVAL OpenDate SECOND
        ),
        INTERVAL @@session.time_zone HOUR_MINUTE
    ),
    INTERVAL 'intended offset' HOUR_MINUTE
)

where 'intended offset' is an offset in the form "[-]HH:MM"

 

[PCHomepage]

Thanks for that. It's not a matter of "insisting on doing things the hard way" but anything I manually add to the connection will simply be overwritten when any page is regenerated by CCS and there may be places in other parts of the site that need local time. Later on, however, once the site has been recoded manually, I can do it the "easy" way.

Your diagnostics were very thorough and quite interesting, using techniques I never would have thought of other than abstractly. I'm still looking it over to try to understand all the details but having said that, I added the last bit of code above to one of the queries and it seems to work perfectly, giving only the 1951 entry that is there and not also showing the 1952 entry:

SELECT tours.ID, Event, EventType, Venue, City, StateName, YearOnly, OpenDate, CloseDate, Notes, CountryName AS CountryText, StateName AS StateText 
FROM (tours LEFT JOIN pchome_geoip.countries c ON c.ID = tours.Country) 
LEFT JOIN pchome_geoip.states s ON s.ID = tours.StateProvence 
WHERE       EventConfirmed = 1 
            AND(
            YEAR(DATE_ADD(
    DATE_SUB(
        DATE_ADD(
            FROM_UNIXTIME( 0 ),
            INTERVAL OpenDate SECOND
        ),
        INTERVAL @@session.time_zone HOUR_MINUTE
    ),
    INTERVAL '08:00' HOUR_MINUTE
) 
            ) LIKE '%1951%')
ORDER BY OpenDate

Also adding it to the year and count listing, it works just as it should:

SELECT YEAR(DATE_ADD(
    DATE_SUB(
        DATE_ADD(
            FROM_UNIXTIME( 0 ),
            INTERVAL OpenDate SECOND
        ),
        INTERVAL @@session.time_zone HOUR_MINUTE
    ),
    INTERVAL '08:00' HOUR_MINUTE
) ) AS YearAdded,
COUNT(OpenDate) AS `NumEntries`
FROM tours 
 WHERE EventConfirmed = 1 
GROUP BY YEAR(DATE_ADD(
    DATE_SUB(
        DATE_ADD(
            FROM_UNIXTIME( 0 ),
            INTERVAL OpenDate SECOND
        ),
        INTERVAL @@session.time_zone HOUR_MINUTE
    ),
    INTERVAL '08:00' HOUR_MINUTE
) )

I presume that I'll need to pass in a dynamic value for 'intended offset' to reflect the site visitors' location offset and if so, I'm sure I can implement that fairly easily.

 

[jpadie]

all you need is to run the query to set the time zone at some point before you need it. there must be a way to run arbitrary queries in codechargestudio without it being overwritten by the overzealous assembler .