Conversion of Python DateTime string into integer milliseconds

Question

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.

Why do you think that storing an integer or milliseconds would require less space than the native datetime format in the database? — Gordon Linoff
– Gordon Linoff, Commented Jun 28, 2015 at 1:41
You could do something like int(datetime.strptime(time_string, your_format).strftime('%s'))*1e3. This would be a float in milliseconds since epoch, which you could cast as int if you wanted. — Scott
– Scott, Commented Jun 28, 2015 at 1:44
@GordonLinoff - I mean less space than the STRING representation. — SMGreenfield
– SMGreenfield, Commented Jun 28, 2015 at 1:46
@SMGreenfield . . . That is not how date/time formats work in the database. Use the native formats. — Gordon Linoff
– Gordon Linoff, Commented Jun 28, 2015 at 1:47
@Scott -- Your suggestion was very close, and exactly the approach. Here's the precise syntax I used: int(Decimal(datetime.datetime.strptime("2015-06-27T02:10:05.653000Z", "%Y-%m-%dT%H:%M:%S.%fZ").strftime('%s.%f'))*1000)) I'm assuming you meant multiplying by 3e8 (1000), not 1e3 (483).... — SMGreenfield
– SMGreenfield, Commented Jun 28, 2015 at 2:39

Community · Accepted Answer · 2017-05-23 12:14:09Z

6

[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"

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jun 28, 2015 at 5:10

Scott

6,4195 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SMGreenfield Over a year ago

I, too, was puzzled why there wasn't a lowercase %s listed in the strftime() docs, but when it appeared to work, I figured someone else must know better. It's still important to note that with the Z parsing string other UTC with offsets wouldn't parse, but my use case SPECIFICALLY was limited to Z (Zulu time). I really appreciate your effort on this! Thanks!

jfs Over a year ago

both questions you've linked have explicit comments on why you should not use .strftime('%s')

Scott Over a year ago

@J.F.Sebastian I have actually used .strftime('%s') in certain cases where timezone was not a factor and therefore was not aware of the problem until I read the linked posts and comments (specifically your comment). Do you think there is an added benefit to explicitly stating the problems with .strftime('%s') or let users follow the links?

jfs Over a year ago

Just put the correct working solution at the top of the answer. At the bottom of the answer you could mention why .strftime('%s') should not be used (or just link to my comment).

Scott Over a year ago

@J.F.Sebastian Thanks for the suggestion. Edited following your advice.

Community · Accepted Answer · 2017-05-23 10:26:47Z

1

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jun 28, 2015 at 20:12

jfs

417k210 gold badges1k silver badges1.7k bronze badges

3 Comments

SMGreenfield Over a year ago

I've read both posts (quite a discussion!) Assuming a ZULU-ONLY UTC (my use case), is the above approach superior to my edit which eliminates strftime? Or just more elegant because it accounts for parsing timezone offsets?

jfs Over a year ago

@SMGreenfield: if we ignore the bare except: and long usage that you should not do then there are only minor differences (validation, rounding behavior, supported python versions). btw, timezone offsets are not parsed; utc time is assumed (zero utc offset).

Scott Over a year ago

That is an incredibly detailed and well referenced answer you give in your link. Will be looking through that for a while.

SMGreenfield · Accepted Answer · 2015-06-28 20:04:57Z

0

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.

edited Jun 28, 2015 at 20:04

answered Jun 28, 2015 at 4:32

SMGreenfield

1,72820 silver badges36 bronze badges

6 Comments

Scott Over a year ago

What I thought was the way to go (and have personally used) turns out to have possible errors. So, unless you live along gmt you might not want to use %s.

Scott Over a year ago

You may already know this but here is some discussion on mysql fractional time/dates stackoverflow.com/a/26299379/4663466 and there is a mysql built in UNIX_TIMESTAMP() dev.mysql.com/doc/refman/5.7/en/…

jfs Over a year ago

don't use .strftime('%s') with utc input unless your local timezone is utc.

Scott Over a year ago

It might be worthwhile looking into just inserting your diatomite objects with format %Y-%m-%dT%H:%M:%S.%fZ using the appropriate mysql DATE type and when you need unix time in milliseconds, using built in mysql UNIX_TIMESTAMP(date) (assuming it deals with fractional seconds and outputs to milliseconds). I have no experience in this so investigate it, but this may be faster than conversion to unix time before insert and possibly conversion to human readable after fetching.

SMGreenfield Over a year ago

@Scott -- before taking that approach, I need to investigate 1) why a mysql DATETIME(6) type gives a warning if the time string ends in 'Z', and 2) how many bytes a DATETIME(6) uses.

|

Collectives™ on Stack Overflow

Conversion of Python DateTime string into integer milliseconds

3 Answers 3

5 Comments

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related