Koji database has an events table that provides a strictly monotonically increasing time. By this monotonic time we can globally sort koji actions and filter/search data. Hence such time is used by many tables

• user_perms
• user_group
• build
• tag_inheritance
• tag_config
• tag_extra
• tag_updates
• build_target_config
• repo
• external_repo_confi
• tag_external_repos
• cg_users
• standard_buildroot
• tag_listing
• tag_packages
• group_config
• group_req_listing
• group_package_listing

Hence we can expect that many methods in koji API have parameters to express the monotonic time. On the same grounds, we can expect that some subcommands of koji cli have options to express the monotonic time, too. E.g., the queryHistory method and list_history subcommand

queryHistory(tables=None, **kwargs)
  description: Returns history data from various tables that support it

    tables: list of versioned tables to search, no value implies all tables
            valid entries: user_perms, user_groups, tag_inheritance, tag_config,
                build_target_config, external_repo_config, tag_external_repos,
                tag_listing, tag_packages, group_config, group_req_listing,
                group_package_listing

    - Time options -
    times are specified as an integer event or a string timestamp
    time options are valid for all record types
    before: either created or revoked before timestamp
    after: either created or revoked after timestamp
    beforeEvent: either created or revoked before event id
    afterEvent: either created or revoked after event id

And following is the help content of the list_history subcommand

[nerd@dhcp-137-35 ~]$ koji list-history --help
Usage: koji list-history [options]
(Specify the --help global option for a list of other help options)

Options:
  -h, --help            show this help message and exit
  --debug               
  --build=BUILD         Only show data for a specific build
  --package=PACKAGE     Only show data for a specific package
  ...
  --before=TIMESTAMP    Only show entries before timestamp
  --after=TIMESTAMP     Only show entries after timestamp
  --before-event=EVENT_ID
                        Only show entries before event
  --after-event=EVENT_ID
                        Only show entries after event
  ...
[nerd@dhcp-137-35 ~]$

But the type of the the time attribute, which expresses the monotonic time value, in the events table is timestamp without time zone. Hence, the value of the monotonic time provided by the events table depends on database timezone setting.

BTW, we can configure timezone for the PostgreSQL in a session level, in a user level, in a database level, and in a host level. Hence, there are two issues

1. even we pass a timestamp with a time zone, the database will avoid the timezone because of the time attribute type is timestamp without time zone.
2. a user does not know the timezone setting of the database because we don't have a method to access events table's timezone setting. Hence a user cannot compute a timestamp with the database timezone from his/hers local time.

BTW, I guess it's most possible that we configure PostgreSQL timezone to be UTC.

Hence to resolve this issue all we need is that, when to provide a timestamp a client (API method/koji cli) must also provide its timezone (in PostgreSQL context, such timestamp with time zone is expressed by the type timestamptz). After that we pass such timestamptz value to the hub, and then before to pass the value to the database query clauses, hub coverts the timestamptz value to another timestamptz value according to the hub timezone setting (strictly speaking, according to the database timezone setting).

For this solution I guess we also need take care of DST(Daylight Saving Time). Hence this solution is some complicated. Seems we really, really, should never ever deal with timezones. Hence we gives several variants of this solution.

If we change the time attribute type from timestamp without time zone to timestamp with time zone, then we only need to provide a timestamp with timezone, then PostgreSQL will do all the computation for us.

Following function will give us a date with timezone:

import datetime
import time
def isotime_with_tz(isotime=None):
    if isotime is None:
        isotime = datetime.datetime.now().isoformat()
    return '%s%+03d:00' % (isotime, -(time.timezone/3600))

def datetime_to_isotime_with_tz(dt=None):
    if dt is None:
        dt = datetime.datetime.now()
    isotime = dt.isoformat()
    return '%s%+03d:00' % (isotime, -(time.timezone/3600))

But perhaps a most simple way is to use a convention that the database timezone is always UTC. Hence a client (a API method or koji cli subcommand) only need provide a timestamp with UTC timezone.

And we can convert a datetime object to UTC as follows

def datetime_to_utc(dt=None):
    if dt is None:
        return datetime.datetime.utcnow().isoformat()
    return datetime.datetime(*(time.gmtime(time.mktime(dt.utctimetuple()))[:-2])).isoformat()

And there is the 3rd way. We have a client (koji API method or koji cli) to pass second since the Epoch as a float. Then hub convert the second to its localtime.

We can covert a datetime to second as follows

def datetime_to_second(dt=None):
    if dt is None:
         dt = datetime.datetime.now()
    return dt.strftime('%s')

def second_to_isotime(second=None):
    if second is None:
        second = time.time()
    dt = datetime.datetime.fromtimestamp(second)
    return dt.isoformat()

And I prefer the way to change the type of the time attribute of the events table to timestamp with time zone.

We can easily alter the type timestamp without time zone to timestamp with time zone, just as this link shows.

I don't think there much point in tracking timezones in the database. It would make them harder to interpret, and we shouldn't really care where an event happened.

The problem in this case is that we're being lazy and letting the db parse the time string.

PR here: https://pagure.io/koji/pull-request/182

Also, not to sidetrack the bug, is there anyway to specify --before/--after using UTC? It didn't appear to be possible, but maybe I overlooked something.

Also, not to sidetrack the bug, is there anyway to specify --before/--after using UTC? It didn't appear to be possible, but maybe I overlooked something.

With the current cli code, the arg is passed to the database as is. Since the field in the db are plain timestamps, I don't think there is a way to do this.

On further thought, we probably should convert to using timestamp with time zone in the db. However, that's a much larger change to make here, and probably has some risks. Even if we do make that change, we still need the PR above since the db does not know the client's timezone.

The PR of @mikem is enough to resolve this issue. I have tried it on the RHEL6, and it works well. +1.

But yes, just as @mikem mentioned, I think yes we should covert to using timestamp with time zone. In koji now it's us who are in charge of compute date/time, and it's not easy to prepare everything in this situation. For example, I found another wrong timestamp (The Finished field is wrong).

[nerd@dhcp-137-35 ~]$ koji buildinfo python-pyroute2-0.3.15-2.el6eng
BUILD: python-pyroute2-0.3.15-2.el6eng [504607]
State: COMPLETE
     ...
Finished: Thu, 21 Jul 2016 00:42:14 CST
Tags: eng-rhel-6-candidate
      ...
[nerd@dhcp-137-35 ~]$ koji call getBuild python-pyroute2-0.3.15-2.el6eng
{'completion_time': '2016-07-21 00:42:14.895312',
 'completion_ts': 1469061734.8953099,
 'creation_event_id': 13638372,
      ...
 'id': 504607,
 'name': 'python-pyroute2',
 'nvr': 'python-pyroute2-0.3.15-2.el6eng',
 'package_id': 58788,
 'release': '2.el6eng',
     ...    }
[nerd@dhcp-137-35 ~]$ LANG=C date -d @1469061734.8953099
Thu Jul 21 08:42:14 CST 2016
[nerd@dhcp-137-35 ~]$ TZ=UTC LANG=C date -d @1469061734.8953099
Thu Jul 21 00:42:14 UTC 2016
[nerd@dhcp-137-35 ~]$

Hence the PR for this issue is just a temporary solution. It's obvious that to use epoch time(seconds since 1970-01-01 00:00:00 UTC) insead of a timestamp string like '2016-07-21 00:42:14.895312' is suppose that both the koji and hub are with UTC time zone. But just as this issue and the above example show that it's difficult for us to deal with date/time ourselves. Hence we should use existed tools or libraries. Just as what this blog told us, PostgreSQL can do all computation for us:

PostgreSQL has a special data type that is recommended called timestamp with timezone, and which can store the timezone associated, and do all the computation for you.

And just as the comment showing, only by standard library we can construct a datetime with time zone.

Sure the timestamp with time zone is a PostgreSQL special date type. But to covert timestamp with out time zone to epoch time, we have used PostgreSQL special method

EXTRACT(EPOCH FROM time)

BTW, different platforms, OSs, and languages could use different epochs.

Hence I think we should covert to using timestamp with time zone.

Resolved by #182