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?

 

[jpadie]

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.

 

[jpadie]

so your query would look like this

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

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

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

 

[jpadie]

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

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

 

[PCHomepage]

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:

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?

 

[jpadie]

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

OpenDate INT(10) NULL

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

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

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

 

[PCHomepage]

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.

 

[jpadie]

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

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
            )

 

[PCHomepage]

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.

 

[PCHomepage]

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

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

 

[PCHomepage]

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.

 

[jpadie]

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

date_default_timezone_set();

for mysql this is

SET time_zone = timezone;

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

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;

 

[PCHomepage]

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?

 

[jpadie]

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/...es/HowTo/CustomizingDataSource/ModifySQL.html

shows how you can dynamically modify the SQL string being used by codecharge studio. and this post

http://docs.codecharge.com/studio3/...es/HowTo/CustomizingDataSource/ModifySQL.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

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.

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.

 

[PCHomepage]

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:

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

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.

 

[jpadie]

this is correct

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

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

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

or

CONCAT('%', '1995','%')

might also work depending on how your manual query functions in codechargestudio.

but the fact that this works

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.

 

[PCHomepage]

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.

'%1955%'

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

'%{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.

 

[jpadie]

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

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

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

select now();

 

[PCHomepage]

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

 

[jpadie]

ok. so there's the problem then.

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

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

but you could also convert explicitly too.

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.