MYSQL Date of Birth Pre 1970 & Timezone

MYSQL Date of Birth Pre 1970 & Timezone

by Al Brocklehurst -
Number of replies: 6

Hi all,

I have an SQL query which works ok but missing the DOB (custom field) for those born before Unix (1970-01-01) and it also reports the day before given server is US but I'm in Adelaide.

SELECT u.firstname AS 'First Name', u.lastname AS 'Last name', DATE_FORMAT(FROM_UNIXTIME(uid.data), '%d-%m-%Y') AS DateofBirth, 
u.email AS email, uid2.data AS 'LEA Site', c.idnumber AS 'System Code', c.shortname AS 'Module Completed',
DATE_FORMAT(FROM_UNIXTIME(ci.timecreated),'%d-%m-%Y') AS 'Certificate Date'
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
JOIN prefix_simplecertificate_issues AS ci ON u.id = ci.userid
WHERE c.id=170 and uif.shortname = 'DOB' and uif2.shortname = 'School'
ORDER BY u.lastname

Any ideas on how to tweak the query to correct the above.

Thanks


Average of ratings: -
In reply to Al Brocklehurst

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Sam Stevens -

Well the DOB thing is odd. MYSQL should store that as a signed integer. So you can go as far back with a negative timestamp as you can forward with a positive one. If you just return the raw integer (uid.date) does it have any negatives in it? I think that is a custom field as well so I'd suspect that it isn't going in properly.

For the times being returned you probably wouldn't change those here. It will be in the timezone settings for the server. Now it could be a couple. Could be the MYSQL or possibly the PHP timezone setting. Google for how to change both of them to oz.

Average of ratings: -
In reply to Sam Stevens

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Al Brocklehurst -

Thanks for the ideas Sam. 

The timestamp is recorded in a custom profile field 'Date of Birth' and is stored in the user_info_data field, where other custom profile data is stored so the (uid_data) can't change to (uid_date) as 'data' is the column name.

Average of ratings: -
In reply to Al Brocklehurst

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Al Brocklehurst -

Some success thanks to Google and persistence

Replaced

DATE_FORMAT(FROM_UNIXTIME(uid.data), '%d-%m-%Y')

with

DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL uid.data SECOND), '%d-%m-%Y')

so now show's pre 1970 dates.

Just need to sort out the timezone issue

Average of ratings: -
In reply to Al Brocklehurst

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Helen Moraes -

Hi Al,

Did you find out how to fix the timezone issue?


Thanks

HM

Average of ratings: -
In reply to Helen Moraes

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Sam Stevens -

As I said a few areas it could be. The main culprits would be the php  and the mysql time zone settings. 

Average of ratings: -
In reply to Helen Moraes

Re: MYSQL Date of Birth Pre 1970 & Timezone

by Al Brocklehurst -

Yes thanks - after doing some more searching I change the query to:

INTERVAL (uid.data + 86400) SECOND)

uid.data is the custom profile table with the DOB in it so added 1 day in seconds

All good smile

Average of ratings: Useful (1)