Conversion of Python DateTime string into integer

问题:

I would like to convert a UTC TimeDate stamp string into an integer value of milliseconds (might need to be a 64-bit quantity), so that it takes up less space when stored in a mySQL database column. This UTC string is being generated from another library, and I store it as a kind of per-user GUID.

Can datetime or dateutil convert this into a single integer value (like "milliseconds since epoch")? Or do I need to do that myself?

Parsing using this approach:

myDateTime = dateutil.parser.parse("2015-06-27T02:10:05.653000Z")
print("Parsed datetime String is {0}, ordinal value is {1}".format(myDateTime, myDateTime.toordinal()))

Gives the output:

Parsed datetime String is 2015-06-27 02:10:05.652999+00:00, ordinal value is 735776

…which only gives an ordinal value for the date. Further, if I have a time with an integer 653 milliseconds, then I want that parsed object to know it has 653 milliseconds, not 652999.

回答1:

[Edited following suggestion in the comments]

Using Ben Alpert's answer to How can I convert a datetime object to milliseconds since epoch (unix time) in Python we can do the following:

from datetime import datetime
def unix_time(dt):
    epoch = datetime.utcfromtimestamp(0)
    delta = dt - epoch
    return delta.total_seconds()

def unix_time_millis(dt):
    return int(unix_time(dt) * 1000)

a = datetime.strptime("2015-06-27T02:10:05.653000Z", "%Y-%m-%dT%H:%M:%S.%fZ")
unix_time_millis(a)

returns:

1435371005653

which is equivalent to: Sat, 27 Jun 2015 02:10:05 GMT (as expected)

We can also use datetime's .strftime('%s') to get unix time, even milliseconds using the following (but this is not advised):

from decimal import Decimal

int(Decimal(datetime.strptime("2015-06-27T02:10:05.653000Z", "%Y-%m-%dT%H:%M:%S.%fZ").strftime('%s.%f'))*1000)

returns:

1435396205653

equivalent to: Sat, 27 Jun 2015 09:10:05 GMT (on my mac in San Diego; Note: this is 7 hours off what we may have expected).

The cause of the error is described by J.F. Sebastian in the comments of the link above and in this answer regarding .strftime('%s') behavior. J.F. Sebastian points out that "it is not supported, it is not portable, it may silently produce a wrong result for an aware datetime object, it fails if input is in UTC (as in the question) but local timezone is not UTC"

回答2:

There are two parts:

• to convert "2015-06-27T02:10:05.653000Z" into a datetime object, see How to parse ISO formatted date in Python?

  import re
  from datetime import datetime
  
  utc_time = datetime(*map(int, re.findall(r'\d+', time_string))
  

• to convert the UTC time to POSIX timestamp as integer milliseconds, see How can I convert a datetime object to milliseconds since epoch (unix time) in Python?

  from datetime import datetime
  
  def timestamp_millis(utc_time, epoch=datetime(1970, 1, 1)):
      td = utc_time - epoch
      return (td.microseconds + (td.seconds + td.days * 86400) * 10**6) // 10**3
  

回答3:

Both Scott and GordonLinoff provided excellent help in solving my issue. I'm adding the answer for completeness.

Python code to convert UTC datetime string to milliseconds since epoch:

EDITED TO ELIMINATE strftime:

from datetime import datetime

def convert_UTC_zulu_string_to_milliseconds_since_epoch(myUTCzuluString):
    try:
        dt_unix = datetime.strptime(myUTCzuluString, "%Y-%m-%dT%H:%M:%S.%fZ")
        epoch = datetime.utcfromtimestamp(0)
        delta = dt_unix - epoch
        millisecondsSinceEpoch = long(delta.total_seconds() * 1000)

    except:
        millisecondsSinceEpoch = 0L

    return millisecondsSinceEpoch


myUTCzuluString = "2015-06-27T02:10:05.653000Z"
millisecondsSinceEpoch = convert_UTC_zulu_string_to_milliseconds_since_epoch(myUTCzuluString)
print("Milliseconds since epoch: {0}".format(millisecondsSinceEpoch))

ALSO: mysql WILL accept a datetime value with milliseconds/microseconds directly from a string IF I've defined the column as a DATETIME(6) datatype:

UPDATE myTable SET myDateTimeField = '2015-06-27T02:10:05.653000Z'

Note that including the "Z" at the end of the UTC datetime string results in a truncation warning from mysql.

I could not determine if the added precision of DATETIME(6) over DATETIME resulted in mysql's InnoDB engine using more than 8 bytes, which was one of the initial reasons for my researching the issue.