Converting DD MM YYYY into YYYY-MM-DD?

Discussion in 'Python' started by Gilles Ganault, Aug 17, 2009.

  1. Hello,

    I need to convert DD MM YYYY dates into the MySQL-friendly
    YYYY-MM-DD, and translate the month name from literal French to its
    numeric equivalent (eg. "Janvier" into "01").

    Here's an example:

    SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
    26 Mai 2007|17 Août 2009 - 09h20

    I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
    respectively.

    What is the best way to do this in Python?

    Thank you.
    Gilles Ganault, Aug 17, 2009
    #1
    1. Advertising

  2. On Aug 17, 3:26 pm, Gilles Ganault <> wrote:
    > Hello,
    >
    >         I need to convert DD MM YYYY dates into the MySQL-friendly
    > YYYY-MM-DD, and translate the month name from literal French to its
    > numeric equivalent (eg. "Janvier" into "01").
    >
    > Here's an example:
    >
    > SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
    > 26 Mai 2007|17 Août 2009 - 09h20
    >
    > I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
    > respectively.
    >
    > What is the best way to do this in Python?
    >
    > Thank you.
    Jonathan Gardner, Aug 18, 2009
    #2
    1. Advertising

  3. Gilles Ganault

    Che M Guest

    On Aug 17, 6:26 pm, Gilles Ganault <> wrote:
    > Hello,
    >
    >         I need to convert DD MM YYYY dates into the MySQL-friendly
    > YYYY-MM-DD, and translate the month name from literal French to its
    > numeric equivalent (eg. "Janvier" into "01").
    >
    > Here's an example:
    >
    > SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
    > 26 Mai 2007|17 Août 2009 - 09h20
    >
    > I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
    > respectively.
    >
    > What is the best way to do this in Python?
    >
    > Thank you.


    Likely this is not the best way, but I would do, for
    the first one (and the same idea for the second):

    def convert(date):
    frenchdict = {'Mai':'May'} #etc...
    day = mystring[:2]
    month = frenchdict[ mystring[3:6] ]
    year = mystring[7:11]
    newdate = year+'-'+month+'-'+day
    print 'newdate is ', newdate
    Che M, Aug 18, 2009
    #3
  4. On Aug 17, 3:26 pm, Gilles Ganault <> wrote:
    >         I need to convert DD MM YYYY dates into the MySQL-friendly
    > YYYY-MM-DD, and translate the month name from literal French to its
    > numeric equivalent (eg. "Janvier" into "01").
    >
    > Here's an example:
    >
    > SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
    > 26 Mai 2007|17 Août 2009 - 09h20
    >
    > I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
    > respectively.
    >
    > What is the best way to do this in Python?
    >


    Unfortunately, there isn't any string to date parsers in the built-
    ins. Not to worry, though, since writing your own is easy, especially
    if you use regular expressions from the re module. I suggest using an
    RE such as:

    r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)"

    If you want to translate month names to month numbers, then you need
    some sort of dict to do so. Unfortunately, there isn't a terrific
    standard for this, so your best bet is to put it in some file
    somewhere, or even hard-code it in your code. (Month names won't
    change over the lifetime of your program, so it's reasonable to put
    them in your code somewhere.)

    month_names_to_numbers = {
    'jan':1, ... }

    Once you have the year, month, and date, formatting it is trivial with
    the built-in formatter.

    "%04d-%02d%02d %02d:%02d" % (year, month, date, hour, minute)

    The variety of date formats out there have prevented a universal,
    clean solution to this problem. Until we all start sticking to the
    same conventions, we will always have to write code to translate dates
    from one format to another.
    Jonathan Gardner, Aug 18, 2009
    #4
  5. Correct me if I'm wrong, but doesn't
    http://docs.python.org/library/datetime.html#datetime.datetime.strptime do
    this?

    >>> import locale
    >>> locale.setlocale(locale.LC_ALL, 'FR')

    'French_France.1252'
    >>> date_str = '05 Mai 2009 - 18h25'
    >>> fmt = '%d %B %Y - %Hh%M'
    >>> date_obj = datetime.strptime(date_str, fmt)
    >>> date_obj

    datetime.datetime(2009, 5, 5, 18, 25)
    >>> date_obj.strftime('%Y-%m-%d %H:%M')

    '2009-05-05 18:25'

    If you're using a recent enough version of Python (2.5 and up) I'd imagine
    that's the best way to do it?

    On Mon, 17 Aug 2009 16:58:28 -0700, Che M <> wrote:

    > On Aug 17, 6:26 pm, Gilles Ganault <> wrote:
    >> Hello,
    >>
    >>         I need to convert DD MM YYYY dates into the MySQL-friendly
    >> YYYY-MM-DD, and translate the month name from literal French to its
    >> numeric equivalent (eg. "Janvier" into "01").
    >>
    >> Here's an example:
    >>
    >> SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
    >> 26 Mai 2007|17 Août 2009 - 09h20
    >>
    >> I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
    >> respectively.
    >>
    >> What is the best way to do this in Python?
    >>
    >> Thank you.

    >
    > Likely this is not the best way, but I would do, for
    > the first one (and the same idea for the second):
    >
    > def convert(date):
    > frenchdict = {'Mai':'May'} #etc...
    > day = mystring[:2]
    > month = frenchdict[ mystring[3:6] ]
    > year = mystring[7:11]
    > newdate = year+'-'+month+'-'+day
    > print 'newdate is ', newdate
    >




    --
    Rami Chowdhury
    "Never attribute to malice that which can be attributed to stupidity" --
    Hanlon's Razor
    408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)
    Rami Chowdhury, Aug 18, 2009
    #5
  6. On Aug 17, 5:20 pm, Ben Finney <> wrote:
    >
    > Instead, you should generate the map based on the standard library (in
    > this case, the underlying C standard library) locale database
    > <URL:http://docs.python.org/library/locale.html?highlight=locale%20date#lo...>:
    >


    Does Windows support POSIX locales?
    Jonathan Gardner, Aug 18, 2009
    #6
  7. On Aug 17, 7:06 pm, Ben Finney <> wrote:
    > Jonathan Gardner <> writes:
    > > Unfortunately, there isn't any string to date parsers in the built-
    > > ins.

    >
    > Fortunately, Python 2.5 or later has the ‘datetime.strptime’ function..
    >


    Hate to weasel out of this one, but the language that strptime
    provides is pretty limited. I don't find it useful except in the
    trivial cases. Same goes for strftime. Also, both of these are very
    Western European centric. Yes, Asian languages are supported but not
    naturally.
    Jonathan Gardner, Aug 18, 2009
    #7
  8. On Aug 17, 5:18 pm, "Rami Chowdhury" <> wrote:
    >
    > >>> import locale
    > >>> locale.setlocale(locale.LC_ALL, 'FR')


    locale is nice when you only have a single thread.

    Webservers aren't single threaded. You can't serve up one page for one
    locale and then another in another locale without seeing very, very
    weird behavior.
    Jonathan Gardner, Aug 18, 2009
    #8
  9. ----
    My sample interactive session (locale.setlocale and all) was on a 32-bit Vista
    install of Python 2.5, so it works on that...

    ---
    Rami Chowdhury
    "A man with a watch knows what time it is. A man with two watches is never
    sure". -- Segal's Law
    408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

    On Monday 17 August 2009 19:46:24 Ben Finney wrote:
    > Jonathan Gardner <> writes:
    > > On Aug 17, 5:20 pm, Ben Finney <> wrote:
    > > > Instead, you should generate the map based on the standard library (in
    > > > this case, the underlying C standard library) locale database
    > > > <URL:http://docs.python.org/library/locale.html?highlight=locale date
    > > >#lo...>:

    > >
    > > Does Windows support POSIX locales?

    >
    > If it does not, it should :) since it addresses the problem in one
    > standard place. It would be foolish for Python to re-implement that
    > functionality when presumably the operating system already knows how to
    > map between dates and locale-specific text representations.
    >
    > You'll need to check the operating system documentation for what
    > alternative it might provide.
    >
    > --
    > \ “I got fired from my job the other day. They said my |
    > `\ personality was weird. … That's okay, I have four more.†|
    > _o__) —Bug-Eyed Earl, _Red Meat_ |
    > Ben Finney
    Rami Chowdhury, Aug 18, 2009
    #9
  10. Thanks everyone for the help. This script is just a one-shot thingie
    on my work host, not as a web script or anything professional.

    On Mon, 17 Aug 2009 17:05:28 -0700 (PDT), Jonathan Gardner
    <> wrote:
    >Unfortunately, there isn't any string to date parsers in the built-
    >ins. Not to worry, though, since writing your own is easy, especially
    >if you use regular expressions from the re module. I suggest using an
    >RE such as:
    >
    > r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)"


    I've never seen regexes like this. I'm curious to know what those
    mean:

    r = Unicode?

    (?P<date> = ? means that it shouldn't be greedy, what about P<date>?
    Gilles Ganault, Aug 18, 2009
    #10
  11. On Tue, 18 Aug 2009 17:10:50 +1000, Ben Finney
    <> wrote:
    >Luckily, you have access to the documentation to find out.


    I never used groups before. Thanks for showing me.

    At this point, the script is almost done, but the regex fails if the
    month contains accented characters (eg. "Août", but fine if eg.
    "Jan").

    Adding a line to load the French locale doesn't help :-/

    Any idea what I could do to keep the regex happy?

    Thank you.

    ==============
    import re
    import apsw
    import locale

    #In case error due to accent in month name, but no soup 4 U
    locale.setlocale(locale.LC_ALL, 'FR')

    connection=apsw.Connection("test.sqlite")
    cursor=connection.cursor()

    re_inscription =
    re.compile(r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)")

    sql = 'SELECT id,dateinscription,dateconnexion FROM mytable'
    rows=list(cursor.execute(sql))
    for row in rows:
    dateinscription = row[1]
    dateconnexion = row[2]

    #Prints OK
    print dateinscription

    m = re_inscription.search(dateinscription)
    if m:
    day = m.group("date")
    month = m.group("month")
    year = m.group("year")
    print "%s-%s-%s" % (year,month,day)
    else:
    print "No go"
    ==============
    Gilles Ganault, Aug 18, 2009
    #11
  12. Could you let me know which platform this is on (Windows, *nix)? It may be a
    locale encoding issue -- the locale.setlocale() function allows the second
    argument to be a tuple of (locale_code, encoding), as below:

    locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))

    Since this is for a one-shot (and presumably threading-agnostic) program, and
    a fairly trivially formatted date-string, I would suggest using
    datetime.strptime
    (http://docs.python.org/library/datetime.html#datetime.datetime.strptime) and
    not regular expressions (which IIRC have Issues with non-ASCII characters).


    ----
    Rami Chowdhury
    "Ninety percent of everything is crap." -- Sturgeon's Law
    408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

    On Tuesday 18 August 2009 00:49:41 Gilles Ganault wrote:
    > On Tue, 18 Aug 2009 17:10:50 +1000, Ben Finney
    >
    > <> wrote:
    > >Luckily, you have access to the documentation to find out.

    >
    > I never used groups before. Thanks for showing me.
    >
    > At this point, the script is almost done, but the regex fails if the
    > month contains accented characters (eg. "Août", but fine if eg.
    > "Jan").
    >
    > Adding a line to load the French locale doesn't help :-/
    >
    > Any idea what I could do to keep the regex happy?
    >
    > Thank you.
    >
    > ==============
    > import re
    > import apsw
    > import locale
    >
    > #In case error due to accent in month name, but no soup 4 U
    > locale.setlocale(locale.LC_ALL, 'FR')
    >
    > connection=apsw.Connection("test.sqlite")
    > cursor=connection.cursor()
    >
    > re_inscription =
    > re.compile(r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)")
    >
    > sql = 'SELECT id,dateinscription,dateconnexion FROM mytable'
    > rows=list(cursor.execute(sql))
    > for row in rows:
    > dateinscription = row[1]
    > dateconnexion = row[2]
    >
    > #Prints OK
    > print dateinscription
    >
    > m = re_inscription.search(dateinscription)
    > if m:
    > day = m.group("date")
    > month = m.group("month")
    > year = m.group("year")
    > print "%s-%s-%s" % (year,month,day)
    > else:
    > print "No go"
    > ==============
    Rami Chowdhury, Aug 18, 2009
    #12
  13. On Tue, 18 Aug 2009 01:11:20 -0700, Rami Chowdhury
    <> wrote:
    >Could you let me know which platform this is on (Windows, *nix)? It may be a
    >locale encoding issue -- the locale.setlocale() function allows the second
    >argument to be a tuple of (locale_code, encoding), as below:
    >
    >locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))


    It's on XP, and I'm using ActivePython 2.5.1.1.
    http://www.activestate.com/activepython/

    Python doesn't like the above:

    #locale.Error: unsupported locale setting
    locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))

    Maybe it was introduced in more recent versions of Python?

    >Since this is for a one-shot (and presumably threading-agnostic) program, and
    >a fairly trivially formatted date-string, I would suggest using
    >datetime.strptime
    >(http://docs.python.org/library/datetime.html#datetime.datetime.strptime) and
    >not regular expressions (which IIRC have Issues with non-ASCII characters).


    If the regex library can only handle basic latin characters, I'll wait
    until a script I'm running is done, and I'll upgrade to the 2.6.2.2 to
    see how it goes.

    Thank you.
    Gilles Ganault, Aug 18, 2009
    #13
  14. > Python doesn't like the above:
    >
    > #locale.Error: unsupported locale setting
    > locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))
    >
    > Maybe it was introduced in more recent versions of Python?

    Hmm, that's odd. According to the docs
    (http://docs.python.org/library/locale.html#locale.setlocale) it's been that
    way since 2.0, but I've just checked this on my Windows (Vista) machine and
    you're right, it returns an error.

    This worked for me on 32-bit Vista:

    locale.setlocale(locale.LC_ALL, 'FR')

    It uses Windows-1252 for the encoding, but that seems to deal with the
    circonflexe in 'Août' just fine, so it should work for this purpose.


    ----
    Rami Chowdhury
    "Never attributed to malice that which can be attributed to stupidity." --
    Hanlon's Razor
    408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

    On Tuesday 18 August 2009 01:19:53 Gilles Ganault wrote:
    > On Tue, 18 Aug 2009 01:11:20 -0700, Rami Chowdhury
    >
    > <> wrote:
    > >Could you let me know which platform this is on (Windows, *nix)? It may be
    > > a locale encoding issue -- the locale.setlocale() function allows the
    > > second argument to be a tuple of (locale_code, encoding), as below:
    > >
    > >locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))

    >
    > It's on XP, and I'm using ActivePython 2.5.1.1.
    > http://www.activestate.com/activepython/
    >
    > Python doesn't like the above:
    >
    > #locale.Error: unsupported locale setting
    > locale.setlocale(locale.LC_ALL, ('FR', 'UTF-8'))
    >
    > Maybe it was introduced in more recent versions of Python?
    >
    > >Since this is for a one-shot (and presumably threading-agnostic) program,
    > > and a fairly trivially formatted date-string, I would suggest using
    > >datetime.strptime
    > >(http://docs.python.org/library/datetime.html#datetime.datetime.strptime)
    > > and not regular expressions (which IIRC have Issues with non-ASCII
    > > characters).

    >
    > If the regex library can only handle basic latin characters, I'll wait
    > until a script I'm running is done, and I'll upgrade to the 2.6.2.2 to
    > see how it goes.
    >
    > Thank you.
    Rami Chowdhury, Aug 18, 2009
    #14
  15. I find it odd that the regex library can't handle European characters
    :-/
    Gilles Ganault, Aug 18, 2009
    #15
  16. On Tue, 18 Aug 2009 10:52:41 +0200, Gilles Ganault <>
    wrote:

    >I find it odd that the regex library can't handle European characters
    >:-/


    Ha, found it! :)

    http://www.regular-expressions.info/python.html

    =========
    # -*- coding: latin-1 -*-

    import locale
    import re

    locale.setlocale(locale.LC_ALL, 'FR')

    re_inscription =
    re.compile(r"(?P<date>\d+)\s+(?P<month>\w+)\s+(?P<year>\d+)",re.LOCALE)

    dateinscription = "11 Août 2008"

    m = re_inscription.search(dateinscription)
    if m:
    day = m.group("date")
    month = m.group("month")
    year = m.group("year")
    print "%s-%s-%s" % (year,month,day)
    else:
    print "Yuck"
    =========

    Thanks everyone!
    Gilles Ganault, Aug 18, 2009
    #16
  17. On Tue, 18 Aug 2009 20:03:47 +1000, Ben Finney
    <> wrote:
    >The principles of handling text in Python: Get it to internal Unicode
    >objects as soon as possible, handle it as Unicode for as long as
    >possible, and only encode it to some byte stream for output as late as
    >possible.


    Thanks much for the tip. I'll keep that in mind when I have strings
    with accents.
    Gilles Ganault, Aug 18, 2009
    #17
  18. Ben Finney wrote:
    >>> The principles of handling text in Python: Get it to internal Unicode
    >>> objects as soon as possible, handle it as Unicode for as long as
    >>> possible, and only encode it to some byte stream for output as late as
    >>> possible.

    > Again, note that these recommendations hold for *any* text in Python,
    > with or without accents; once you accept that text is best handled in
    > Unicode, there's little sense in making an exception for the limited
    > subset that happens to be representable in ASCII.


    If the QOTW wasn't meant for fun, I'd vote for this. This is very good advice.

    Stefan
    Stefan Behnel, Aug 18, 2009
    #18
  19. Gilles Ganault

    Mark Tolonen Guest

    "Gilles Ganault" <> wrote in message
    news:...
    >I find it odd that the regex library can't handle European characters


    It can. Read the documentation about the re.LOCALE flag.

    -Mark
    Mark Tolonen, Aug 20, 2009
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kenneth
    Replies:
    2
    Views:
    13,838
    Kenneth
    Jan 14, 2004
  2. Ashraf Ansari

    convert MM/dd/yyyy format into dd/MM/yyyy

    Ashraf Ansari, Aug 30, 2007, in forum: ASP .Net
    Replies:
    4
    Views:
    772
    Alexey Smirnov
    Aug 30, 2007
  3. J P Singh
    Replies:
    4
    Views:
    269
    J P Singh
    Feb 10, 2004
  4. Replies:
    5
    Views:
    242
  5. Matt Gyton

    Converting DD/MM/YYYY date format to UTC

    Matt Gyton, Aug 17, 2006, in forum: Javascript
    Replies:
    1
    Views:
    364
    Dr John Stockton
    Aug 17, 2006
Loading...

Share This Page