INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pulling Our Pre-1970 Years from Unix Timestamp

Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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:

CODE

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?

RE: Pulling Our Pre-1970 Years from Unix Timestamp

Two solutions come to mind.

1. Treating the column as an integer add an arbitrarily large number to the value. Say 100 years worth of seconds. Then convert the integer to a datetime and then subtract 100 years using date_subtract and an interval of 100 years

2. Assume that all datetimes on that column are zero and the convert using intervals. Date_add(from_unixtime(0) interval colname seconds)

2. Is the better solution IMO. But I thought I'd throw them both in to the mix.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

so your query would look like this

(no need for complex ordering as it is already an integer column)

CODE

SELECT DISTINCT
            DATE_FORMAT(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    ), '%Y') AS YearAdded 
FROM        tours 
WHERE       EventConfirmed = 1 
ORDER BY    OpenDate ASC 

and to get the number of tours in each year

CODE

SELECT 
            DATE_FORMAT(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    ), '%Y') AS YearAdded,
            COUNT(OpenDate) as `Num Tours`
FROM        tours 
WHERE       EventConfirmed = 1 
GROUP BY    DATE_FORMAT(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    ), '%Y') ASC 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

you could also use the more compact 'YEAR' function instead of date_format

CODE

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

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
Each of them works beautifully, thank you! The last was a bit cleaner so, with some slight modifications to alias names, that's the one I used.

In the meantime I realized that I also need a way on a different page to "reverse engineer" it by feeding in a year to get back the appropriate entries:

CODE

SELECT * FROM tours 
WHERE YearAdded = 1955
AND EventConfirmed = 1 

for example, with 1955 being fed in as a variable. Getting the variable into the query isn't a problem but I'm not sure how to fetch only the needed year's entries (and, of course, to get all entries when the year isn't specified). Is there a way to do that?

RE: Pulling Our Pre-1970 Years from Unix Timestamp

you must make sure that OpenDate is set to allow NULL values

CODE

OpenDate INT(10) NULL 

then make sure that all current zero values in OpenDate are set to NULL

CODE

UPDATE tours
SET OpenDate = NULL
WHERE OpenDate = 0 

Of course if the date value is actually supposed to be midnight on 1st Jan 1970 you will need to fix those records by hand

then the following query will work for selecting

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
AND         YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) = 1995
OR          OpenDate IS NULL
OR          OpenDate = '' 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
Great again, it works perfectly although when I submit nothing, it gets no results. In the absence of a value it needs to pull up all. FYI, OpenDate is set to allow NULL but it is also a required field in the administration form and will never be empty.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

then you should change your form, or test for zero/empty and submit NULL.

if you want to make the _assumption_ that a zero value is equivalent to empty then you can do this

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) = 1995
            OR          OpenDate IS NULL
            OR          OpenDate = ''
            OR          OpenDate = 0
            ) 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
I suspect we're not understanding one another. openDate is never NULL or empty nor does it ever have only 0 in it. I am submitting tours.php?Year=1955 to get the entries for a specific year and that's working perfectly but if I submit only tours.php without any parameters I want to pull up all dates and years.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
I thought something like this might work but it gives an unspecified syntax error:

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) = 1995
            OR          OpenDate IS NOT NULL
            ) 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
I just noticed that when I select events for 1951, there is one on January 1, 1952 with no time that shows up in the listing and it also is in the count of the entries for 1951. It's not a major problem but can 1951 pull only 1951? Otherwise I'll have to give the time a few seconds after midnight, then filter it from showing on the page.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

the last post above looks like a timezone issue. ensure that you are using the right timezone for all your date manipulations.

For php this is

CODE

date_default_timezone_set(); 

for mysql this is

CODE

SET time_zone = timezone; 

for your earlier post, surely you would just condition the query in your server side language (php)

CODE

if(isset($_GET['year'])):
 $sql = <<<SQL
SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) = 1995
SQL;
else:
$sql = <<<SQL
SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
SQL;
endif; 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
Because the dates are finite and not related to the visitors' geographic locations, the timezone is all GMT throughout and I said in my initial post that I would submit the Year value as a variable which I am doing and that part works as it should except for the one single entry which isn't a major problem.

However, because of the IDE in which this is being done, the SQL is otherwise separate from the PHP so I have the choice of either passing values to the SQL or not. When no value is being passed, it needs to fetch all entries. That said, is there a way to do it in SQL alone?

RE: Pulling Our Pre-1970 Years from Unix Timestamp

I wonder whether mysql knows it should be in GMT? Unless you expressly tell it, it will adopt the system time. Anyway, with 100% certainty, if a date that should be in 1951 is being returned in 1952 then this is a timezone issue, either in the original conversion within php or in the database retrieval.

this post here: http://docs.codecharge.com/studio3/html/index.html... shows how you can dynamically modify the SQL string being used by codecharge studio. and this post http://docs.codecharge.com/studio3/html/index.html... is more specifically about how to modify the where clause.

So now you have a way to modify the query; which is a much better solution than what I am going to propose below. I have not tested any of the following - I'm not at all sure that they will work

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) LIKE concat('%', 1995,'%') 
this should work because if there is no variable provided the comparison should collapse to '%%' which should select everything.

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND IF(
                '1955' != '', 
            (
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ),
            '') = 1955 

this should change the where clause depending on whether the variable is empty.

the best solution, as stated, is to modify the query; particularly since it has been shown to be possible from the IDE's own documentation.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
Thank you. Yes, I know CodeCharge Studio inside and out so I do know how to change the query dynamically if it were created within the GUI. However, CCS has the option of creating a query manually which is not modifiable dynamically and that is what was done in this case due to the extreme modifications needed. Therefor the query itself must be able to do the job and hence my question.

That said, your first example worked perfectly with a slight modification of removing the single quotes and commas from the year variable. With them there, it gave a syntax error and the second example also gave a syntax error:

CODE

SELECT      *
FROM        tours 
WHERE       EventConfirmed = 1 
            AND(
             YEAR(
                DATE_ADD(
                    FROM_UNIXTIME(0), 
                    INTERVAL OpenDate SECOND
                    )
            ) LIKE concat('%1955%') 

Of course the query in my posting was quite simplified over the one I am actually using, which pulls only specific columns and even has a join to a different database but it works now as it should. By the way, your code for counting the entries in the first part of this posting was something I didn't know I wanted until I saw it. Thank you for thinking to include it and thank you for all your help.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

this is correct

CODE

concat('%', 1995,'%') 

if there is a syntax error it could be because the value being passed might be NULL. in which case perhaps this

CODE

concat('%', IFNULL(1995,''),'%') 

or

CODE

CONCAT('%', '1995','%') 
might also work depending on how your manual query functions in codechargestudio.

but the fact that this works

CODE

concat('%1955%') 
suggests that codecharge studio, underneath all the veneer, is just generating php. so you could always go and find the generated php and manually edit it.

in any case; if that works then losing the CONCAT and surrounding brackets will be cleaner and also work.

good luck.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
Yes, it sometimes is an empty value being passed for the year but the OpenDate column in the database is never null.

Actually, it isn't necessary to use CONCAT at all since the wildcard by itself was all that was needed.

CODE

'%1955%' 

Or, in this case, it is a variable passed using:

CODE

'%{Year}%' 

Also about UTC, it is expressly set in the database connection and in the PHP but I've noticed a couple other instances of the year selector pulling up dates that do not exist. I'm wondering if FROM_UNIXTIME() knows it's UTC and, if not, can it be expressed in the query, perhaps using something like @@session.time_zone,'UTC'?

Since the site needs to use absolute dates without relation to any specific locale, I've tried to use UTC throughout but obviously I've missed something since FROM_UNIXTIME() is converting to to my local time for some reason. Checking the timestamps on any of the various online calculators show it to be what it should be for UTC so the timestamps themselves are correct.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

Quote (PCHomepage)


Actually, it isn't necessary to use CONCAT at all since the wildcard by itself was all that was needed.

Quote (jpadie)


losing the CONCAT and surrounding brackets will be cleaner and also work.

Quote:


I'm wondering if FROM_UNIXTIME() knows it's UTC

mysql will use the actual or interpolated value of timezone for that session. a unix timestamp (if properly converted) will always be delocalised to utc. so the most likely place to look will always be at the place where the timestamp is converted back to a date. in this case that is mysql.

what does this query report

CODE

select now(); 

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
select now(); gives my local date and time as you suspected.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

ok. so there's the problem then.

it would always be better to set the timezone expressly on connection

CODE

set @@session.time_zone = "+0:00" 

but you could also convert explicitly too.

CODE

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

i'm not sure whether that will actually fix all issues as a zero offset might still take account of daylight savings. ideally you would convert to utc rather than an offset but for that to work you need to have the timezone table populated and thus have root access to mysql.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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:

CODE

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:

CODE

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.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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.

CODE

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?

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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."

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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'

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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

CODE

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

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

please provide the dataset row with the offending data.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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

CODE

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:

CODE

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 (www.epochconverter.com) it shows properly as:

Quote:

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

RE: Pulling Our Pre-1970 Years from Unix Timestamp

There was a gotcha in the mysql manual.

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

CODE

<?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

CODE

++++++++++++++++
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

CODE

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

CODE

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

CODE

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"

RE: Pulling Our Pre-1970 Years from Unix Timestamp

(OP)
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:

CODE

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:

CODE

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.

RE: Pulling Our Pre-1970 Years from Unix Timestamp

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 .

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close