Re: [sqlite] Date/Time Pains
jason weaver wrote: > I've searched and searched but haven't found anything that really answers > this question. If I've missed something, please point me in the right > direction. > > I want to put the "right" type of timestamp in my dbase. According to my > research, the "right" type is like this: > - create table my_table(date_stuff real); Where did you find the "right" way to do timestamps? I've always used unixtimes and never had a problem. It's extremely portable across all systems and languages. Working with Julian datetimes is a little more complicated, in my experience. - Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
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
Re: [sqlite] Date/Time Pains
jason weaver 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.
>
>
>
> Thank you,
>
> Chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
In your case you can just lift the code from the Sqlite date and time
routines and include it in your application to get the correct time as a
REAL. Alternatively you can store the timestamp in some other way, say
in ISO format and use the Sqlite functions to convert it to an Sqlite
type timestamp when you insert.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
On Mon, Sep 22, 2008 at 08:25:34AM -0500, jason weaver scratched on the wall:
> What is the julianday("now") equivalent in python? I can't find a simple,
> straight-forward answer to this question.
Just ask SQLite for the time:
SELECT julianday('now');
(It is single quotes, BTW).
This should require no locks, no database I/O, and pretty much no
other resources from SQLite, so you can call it each time you sample
a new record. You can then queue them up and and actually insert
them as needed. Asking SQLite also means you use the exact same
date translation code for all operations, which is always a good thing.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
Instead of doing julianday('now') why not put a text-based timestamp
into whatever object you're queueing up and pass that into the
julianday() function to convert it to a real.
On Mon, Sep 22, 2008 at 9:51 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/22/08, 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.
>
>
> I have no idea what the julianday("now") equivalent in Python is, but
> why not let SQLite do the work? Here is how I would do it with Perl...
> (mix of pseudo-code and Perl ahead)
>
> # prepare statement
> $sth = $dbh->prepare(qq{
> INSERT INTO table (somecol, timestamp)
> VALUES (?, julianday("now"))
> });
>
> open transaction...
>
> # loop through your readings
> $sth->execute($somecol);
>
> end transaction...
>
> $dbh->commit;
>
> or throw error...
>
> The above works for me.
>
> Keep in mind though... Igor's caveat about not having enough
> resolution for timing might apply. So, you might have to use some kind
> of high resolution timer. CPAN has one us Perl aficionados...
> conveniently, it is called Time::HiRes
>
>
> I am sure you have one for your Python world as well. If you decide to
> use something like that, then you will have to modify your code
> accordingly.
>
>>
>>
>>
>> Thank you,
>>
>> Chris
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
On 9/22/08, 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.
I have no idea what the julianday("now") equivalent in Python is, but
why not let SQLite do the work? Here is how I would do it with Perl...
(mix of pseudo-code and Perl ahead)
# prepare statement
$sth = $dbh->prepare(qq{
INSERT INTO table (somecol, timestamp)
VALUES (?, julianday("now"))
});
open transaction...
# loop through your readings
$sth->execute($somecol);
end transaction...
$dbh->commit;
or throw error...
The above works for me.
Keep in mind though... Igor's caveat about not having enough
resolution for timing might apply. So, you might have to use some kind
of high resolution timer. CPAN has one us Perl aficionados...
conveniently, it is called Time::HiRes
I am sure you have one for your Python world as well. If you decide to
use something like that, then you will have to modify your code
accordingly.
>
>
>
> Thank you,
>
> Chris
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
"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.
Thank you,
Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date/Time Pains
"jason weaver" <[EMAIL PROTECTED]> wrote
in message
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. ;)
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).
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date/Time Pains
I've searched and searched but haven't found anything that really answers
this question. If I've missed something, please point me in the right
direction.
I want to put the "right" type of timestamp in my dbase. According to my
research, the "right" type is like this:
- create table my_table(date_stuff real);
And for "right" way to store the dates, when I do inserts I should do the
equiv of this:
- insert into my_table values(julianday('now'));
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. ;)
I need a python equivalent of the Sqlite julianday. It seemed so simple of
a question. But I won't kid you that I'm just a little bit confused by the
python representations of date and time. Can someone point me to what I
need?
Thank you!
Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
