-1

I'm having a nightmare of a time trying to get my dates in a suitable format to use across all of these things.

First I started with using datetime.datetime.now() which works great in Python, and should allow me to compare them in sqlite when selecting from the database.

Google Charts however insists that my date and datetime objects be JavaScript objects in the form "Date(YYYY,MM,DD,hh,mm,ss)" and doesn't seem to accept a date in any other format.

So, I started putting the dates in my database in this latter format only to realise I can't select particular dates because the notation is incompatible and I can't work on them in Python.

Someone please help, what's a way I can get my dates compatible across all 3 products?

Hamid
  • 4,410
  • 10
  • 43
  • 72
  • 1
    Probably I don't get your question… Why do you want to use just one format? What's wrong with storing in the DB whatever is easier to work with and then converting explicitly to other formats as you need them? – kirelagin Jun 11 '13 at 23:24
  • Why can't you store them as is convenient in your database, and then convert them into javascript using whatever you'd like after you run the query to pull them out? – jmac Jun 11 '13 at 23:35
  • Nothing I suppose, but I don't know how to go from "2013-06-12 00:33:37.423534" to "Date(YY,MM,DD,hh,mm,ss)". This would probably solve my problem. – Hamid Jun 11 '13 at 23:36

2 Answers2

0

First of all, have a look at this answer to see how to store your datetimes in the DB.

Next, when you'll retrieve it from the DB, you'll get an ordinary datetime object. You'll just format it according to whatever format you need (e.g. with d.strftime('Date(%Y,%m,%d,%H,%M,%S)')).

>>> n = dt.datetime.now()
>>> n.strftime('Date(%Y,%m,%d,%H,%M,%S)')
  > 'Date(2013,06,12,03,42,52)'
Community
  • 1
  • 1
kirelagin
  • 13,248
  • 2
  • 42
  • 57
  • Ok, but if I store the datetimes according to the format in the other post I can't then query the database for times between a certain period because of the format, can I? – Hamid Jun 11 '13 at 23:50
  • @Hamid Sure, you can. `sqlite3` will handle all neccessary convertions for you (for example, see [this question](http://stackoverflow.com/questions/9477175/how-do-you-do-a-datetime-based-query-using-sqlite-in-python)). – kirelagin Jun 12 '13 at 00:01
  • awesome, that works. Note, that in the answer linked from yours the critical part is the sqlite3.PARSE_DECLTYPES when connecting to the database, otherwise you get a string back and so can't call strftime on it. – Hamid Jun 12 '13 at 00:07
  • I believe I have an issue with this, if I'm not mistaken, the JavaScript date format has a zero indexed month... – Hamid Jun 12 '13 at 08:54
  • @Hamid It seems to be a separate question. Anyway, there is no such thing as “JavaScript” format, but you are right, there are different ways to create `Date`s in JS, and the format you've suggested is using zero-based month numbers. Just pick [another one](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date). – kirelagin Jun 12 '13 at 09:00
  • @Hamid Well, it seems that JS can create `Date` objects from a string in ISO format. You can easily get this from your `datetime`: `n.isoformat()`. – kirelagin Jun 12 '13 at 09:01
  • It's the same question I suppose because this issue makes your (accepted) answer wrong, since the month it outputs is actually July (in this example) not June. I'll give the ISO format a go when I'm home tonight. – Hamid Jun 12 '13 at 10:04
  • Iso format doesn't work, it appears Google Charts only supports the single format given in your answer, I just need an alternative that lets me zero index the month. – Hamid Jun 13 '13 at 17:38
  • @MartijnPieters Good to see that you read through the comments. – kirelagin May 03 '17 at 12:17
  • 1
    @kirelagin: haha, and apologies. The format is actually browser dependent so I'm retracting the suggestion to use the ISO format. The error of the zero-based month and the padded integers remains however. – Martijn Pieters May 03 '17 at 12:31
0

After discussing the issue with a colleague that is more savvy with storing time data for outside consumption I came to the conclusion that storing the date in the database using an ISO format string (standardised) is the way to go.

The general gist, is that data storage should be de-coupled from the consumption, and the fact that Google Chart doesn't support this format, should not affect how the data is stored and manipulated on the server. If the data was stored in such a form, it would restrict the future consumption to Google Chart or other means that support it, which is a no-no for scalability and future-proofing the data that is stored.

The result was to store the date normally, as an ISO string which can be parsed into a time or datetime in python, or a Date in Java, but more importantly, returned in it's standard format for client side manipulation according to the requirement therein.

This means, in my case, that the dates are returned to the client as-is, and parsed into a useable format using JavaScript (or any other means depending on how the data is to be consumed).

Hamid
  • 4,410
  • 10
  • 43
  • 72