Bug 1418569 - UTC issue/update date from updatesinfo.xml stored into DB (running in EST, on system in EST which is "-05") with "-04"
Summary: UTC issue/update date from updatesinfo.xml stored into DB (running in EST, on...
Keywords:
Status: CLOSED WONTFIX
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Satellite Synchronization
Version: 580
Hardware: Unspecified
OS: Unspecified
unspecified
medium
Target Milestone: ---
Assignee: Jan Dobes
QA Contact: Red Hat Satellite QA List
URL:
Whiteboard:
Depends On:
Blocks: sat58-nth
TreeView+ depends on / blocked
 
Reported: 2017-02-02 07:09 UTC by Jan Hutař
Modified: 2018-07-13 15:01 UTC (History)
3 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2018-07-13 15:01:44 UTC
Target Upstream Version:


Attachments (Terms of Use)

Description Jan Hutař 2017-02-02 07:09:54 UTC
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@redhat.com" 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?).

Comment 2 Jan Dobes 2017-02-03 10:40:49 UTC
There is also following thing, updateinfo date format is not really consistent, in single updateinfo I can find:

<update status="final" from="release-engineering@redhat.com" version="7" type="bugfix" >
    <id>RHBA-2015:0335</id>
    <issued date="2015-03-05 05:42:10 UTC" />
    ...

<update status="final" from="release-engineering@redhat.com" 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?

Comment 3 Tomas Lestach 2017-02-03 12:21:34 UTC
$ 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.

Comment 4 Jan Hutař 2017-02-03 12:22:14 UTC
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.

Comment 5 Jan Dobes 2017-02-03 13:42:04 UTC
(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


Note You need to log in before you can comment on or make changes to this bug.