Re: Getting the time during a transaction

David Pratt wrote:
> This seems to work fine after all..
> 
> select tid, current_timestamp from ...
> 
> 
> David Pratt wrote:
>> Hi, I am continuing to through a postgres to sqlite conversion and have 
>> run into this sort of thing.
>>
>> select tid, extract(epoch from current_timestamp) ...
>>
>> extract(epoch from current_timestamp) is postgres functionality but is 
>> there a way to obtain the current_timestamp or similar from sqlite 
>> (where I can then obtain epoch with my program logic instead)? I need to 
>> use this for time comparisons but without storing it. Many thanks.


Well I was just ignorantly working through a worked example
of how to bind Python functions into the database, thinking
that there was no current_timestamp functionality in sqlite.
You've just proved me wrong, but I append it below as perhaps
it might be useful.

------------------------

The good news is that pysqlite has this *fantastically easy*
ability to bind in your own functions which will then work
seamlessly at the database level.

Let's define epoch () as returning the year of a timestamp
in time.ctime format, and bind it and some now function
into a database connection:

<code>
import time

def epoch (timestamp):
   """Return the year from a Python ctime value"""
   return time.strptime (timestamp)[0]

from pysqlite2 import dbapi2 as sqlite

db = sqlite.connect (":memory:")
db.create_function ("now", 0, time.ctime)
db.create_function ("epoch", 1, epoch)

for row in db.execute ("SELECT now ()"): print row

for row in db.execute ("SELECT epoch (now ())"): print row

</code>

TJG