Description of problem: UTC formatted (without timezone specified) issue/update date from updatesinfo.xml stored into DB (running in EST, on system in EST which is "-05") with "-04" Version-Release number of selected component (if applicable): spacewalk-backend-cdn-2.5.3-56.el6sat.noarch How reproducible: always Steps to Reproduce: 1. Sync some channel and its errata and check issue and update date in DB Actual results: # date +"%Z %z" EST -0500 # echo "show timezone" | spacewalk-sql -i TimeZone ------------ US/Eastern (1 row) # echo "SELECT advisory,issue_date,update_date FROM rhnerrata WHERE advisory LIKE 'RHBA-2014:1216%';" | spacewalk-sql -i advisory | issue_date | update_date ------------------+------------------------+------------------------ RHBA-2014:1216-1 | 2014-09-16 00:00:00-04 | 2014-09-16 00:00:00-04 (1 row) # vim /var/cache/rhn/reposync/NULL/rhel-ha-for-rhel-5-server-rpms__5Server__x86_64/e82f713698711fe19b115992b7b1f6b1-updateinfo.xml.gz [...] <update status="final" from="security" version="1" type="bugfix" > <id>RHBA-2014:1216</id> <issued date="2014-09-16" /> [...] <updated date="2014-09-16 00:00:00" /> [...] Expected results: I'm not sure and reporting mostly for awareness, but I would expect that timezone-less value in updateinfo.xml means "UTC", but value gets stored with "-04:00". Another strange thing is that DB & system runs in "05:00", but value in DB is in "-04:00" (maybe somehow connected with DST?).
There is also following thing, updateinfo date format is not really consistent, in single updateinfo I can find: <update status="final" from="release-engineering" version="7" type="bugfix" > <id>RHBA-2015:0335</id> <issued date="2015-03-05 05:42:10 UTC" /> ... <update status="final" from="release-engineering" version="1" type="bugfix" > <id>RHBA-2015:0978</id> <issued date="2015-05-12 00:00:00" /> ... However, the date is truncated to the length of 19 thus UTC is stripped before import anyway. When I look into database how it's imported: show timezone; TimeZone --------------- Europe/Prague select advisory_name, issue_date from rhnerrata e inner join rhnchannelerrata ce on e.id = ce.errata_id where ce.channel_id = 132; advisory_name | issue_date ----------------+------------------------ RHSA-2015:1834 | 2015-09-22 00:00:00+02 RHBA-2016:2365 | 2016-11-03 06:09:18+01 RHSA-2015:0265 | 2015-02-24 00:00:00+01 RHBA-2015:0456 | 2015-03-05 00:00:00+01 RHBA-2015:2363 | 2015-11-19 13:38:27+01 RHSA-2015:0988 | 2015-05-12 00:00:00+02 RHSA-2015:1982 | 2015-11-04 00:00:00+01 RHBA-2016:2411 | 2016-11-03 06:09:20+01 RHSA-2016:0071 | 2016-01-27 00:00:00+01 RHSA-2016:0073 | 2016-01-27 00:00:00+01 RHBA-2015:0533 | 2015-03-05 00:00:00+01 RHBA-2016:2386 | 2016-11-03 06:09:19+01 RHBA-2016:2540 | 2016-11-03 06:09:27+01 RHBA-2015:2571 | 2015-12-08 10:15:34+01 RHBA-2016:2424 | 2016-11-03 06:09:21+01 RHEA-2016:2399 | 2016-11-03 06:09:20+01 RHBA-2015:0450 | 2015-03-05 00:00:00+01 RHBA-2016:2151 | 2016-11-03 06:09:06+01 RHBA-2015:2103 | 2015-11-19 13:43:36+01 RHBA-2015:0469 | 2015-03-05 00:00:00+01 RHEA-2015:1473 | 2015-07-22 00:00:00+02 RHEA-2016:0026 | 2016-01-12 16:20:59+01 RHBA-2016:2211 | 2016-11-03 06:09:10+01 RHBA-2015:0357 | 2015-03-05 00:00:00+01 RHSA-2015:2108 | 2015-11-19 14:43:07+01 RHBA-2016:2160 | 2016-11-03 06:09:07+01 RHBA-2016:2464 | 2016-11-03 06:09:23+01 RHEA-2014:1760 | 2014-10-30 00:00:00+01 RHSA-2016:1486 | 2016-07-26 05:06:35+02 RHBA-2016:2168 | 2016-11-03 06:09:07+01 RHBA-2016:1276 | 2016-06-23 14:50:35+02 RHBA-2016:2376 | 2016-11-03 06:09:19+01 RHBA-2016:2271 | 2016-11-03 06:09:13+01 RHBA-2016:2270 | 2016-11-03 06:09:13+01 RHSA-2015:2401 | 2015-11-19 13:44:38+01 ... Basically everything is in my local timezone, only thing which vary is DST. I think it's bug. But how to deal with it? Treat all timestamp as UTC or implement some deeper logic handling possible values which can occur in updateinfo?
$ grep issue_date schema/spacewalk/common/tables/rhnErrata.sql issue_date timestamp with local time zone So the fact issue date is stored in local tz is correct. We need to handle all the rest accordingly. Please, also compare with satellite-sync behavior.
That 1 hour DST difference is not important IMO. I was commenting on the fact that TZ-less date of "2014-09-16 00:00:00" gets imported as "2014-09-16 00:00:00-04", making it 4 hours off. I was thinking that maybe new sync tool or new satellite release might be good way how to fix this.
(In reply to Tomas Lestach from comment #3) > $ grep issue_date schema/spacewalk/common/tables/rhnErrata.sql > issue_date timestamp with local time zone > > So the fact issue date is stored in local tz is correct. We need to handle > all the rest accordingly. Please, also compare with satellite-sync behavior. Also note "timestamp with local time zone" is used only on Oracle, on PostgreSQL we are using "timestamp with time zone". https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm https://www.postgresql.org/docs/9.5/static/datatype-datetime.html