On 2008 September 22 (Mon) 06:25:34am PDT, jason weaver <[EMAIL PROTECTED]>
wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
>>> However, due to database locking issues, I need to do a bunch of
>>> inserts in one transaction or batch. Thus, I store them in a simple
>>> queue. Therefore, the julianday('now') won't work because all of my
>>> batch inserts will have the same date and time. And that doesn't
>>> work very well. ;)
>
>> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>> You will have to deal with this in any case. E.g. on Windows the system
>> timer resolution is 15ms by default. You can insert quite a few records
>> in 15 ms.
>> It is unrealistic to expect that every record could be inserted with a
>> unique timestamp. Find some other way to ensure uniqueness (e.g. just
>> assign a sequential number to each).
>
>
> Thanks for your response. However, my batch inserts and the uniqueness of
> my timestamps aren't the issue. I guess I didn't explain my situation well
> enough. Let me try again.
>
>
>
> I take readings every X seconds which gives me plenty of uniqueness for each
> reading. I save the SQL statements and then insert them in small batches.
> However, from reading this newsgroup I've learned that the correct way to
> put dates into SQLite is as I described before:
>
> - create table my_table(date_stuff real);
>
> - insert into my_table values(julianday('now'));
>
> In my batch loop, I can't use julianday("now") - I need the timestamp
> to reflect when I took the reading.
>
>
>
> If the right way to put datetime in the dbase is the julianday('now')
> format, I need to be able to create and capture that format in python.
>
>
>
> What is the julianday("now") equivalent in python? I can't find a simple,
> straight-forward answer to this question.
There isn't a Python equivalent of julianday(). Well, you could write
one yourself, and it's not that hard, but it's doesn't come with Python,
but you don't need it.
See, julianday() can convert a lot of different strings that represent
dates and times into the Julian day equivalents. What you want is the
equivalent of 'now', and that's easy to get!
>>> import time
>>> time.strftime("%Y-%m-%dT%H:%M:%S",time.gmtime())
'2008-09-22T16:26:10'
Then you use the string that you get and pass that into the julianday()
function in SQLite
insert into my_table values(julianday($string_that_strftime_gave_you));
And that's that.
This only gives you resolution down to a second. If you want
subsecond resolution, then you'll have to use time.time() in Python.
That gives you the number of seconds (which could be a non-integer)
since the epoch. If you're on a Unix machine, then things are easy:
julianday($secs_since_epoch, 'unixepoch')
is what you want.
The problem is that you might be on a different machine where the epoch
might be different. I'm not sure if Python always gives you the Unix
epoch or not -- you'd have to check. But you can still deal with different
epochs.
First, save the string representing the zero point of the epoch
>>> import time
>>> time.strftime("%Y-%m-%dT%H:%M:%S", time.gmtime(0))
'1970-01-01T00:00:00'
You only need to do this once at the beginning of the program.
Use the time.time() string as before.
Then what you want is
julianday($epoch_string, '$secs_since_epoch seconds')
And julianday will just start at the epoch and add time.time() to it,
which is what you wanted.
Another thing you can do is just ask SQLite for julianday('now') at the
appropiate time, and save the real number it gives you for later use.
select julianday('now');
This might be easier.
But do you really need to use the Julian day format? What do you do with
these dates and times?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
