Re: [sqlite] cross-database time function.
Hello! On Tuesday 29 December 2009 04:21:07 Simon Slavin wrote: > I agree that this is often an acceptable alternative. But > > * it's hard to decipher if you're reading the data by eye SQLite internal juliandays format is not human readable too. > * the system does not deal with leap seconds correctly It's not the problem becouse the format precision is 1 second. > * the system terminates in 2038 (if you use Unix's old 32-bit standard) > * one day you may need to read the data on a non-unix platform In cross-platform Tcl: tclsh8.5 [~]clock format 1000 Wed Nov 16 12:46:40 MSK 5138 > Nevertheless, if your data starts off as a Unix epoch, it can be fast and > convenient to just store it without having to do any conversion. As example, Cisco devices and some Unix daemons produce datetime in this format. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross-database time function.
On 28 Dec 2009, at 8:07pm, Alexey Pechnikov wrote: > On Monday 28 December 2009 21:29:48 Simon Slavin wrote: > >> mmdd(if you need just a date) >> hhmmss (if you need just a time) >> mmddThhmmss (the date, then a 'T', then the time) > > Hm, you did forget the simplest way - to use unixepoch format. > And this is very compact too. I agree that this is often an acceptable alternative. But * it's hard to decipher if you're reading the data by eye * the system does not deal with leap seconds correctly * the system terminates in 2038 (if you use Unix's old 32-bit standard) * one day you may need to read the data on a non-unix platform Nevertheless, if your data starts off as a Unix epoch, it can be fast and convenient to just store it without having to do any conversion. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross-database time function.
Hello! On Monday 28 December 2009 21:29:48 Simon Slavin wrote: > mmdd(if you need just a date) > hhmmss (if you need just a time) > mmddThhmmss (the date, then a 'T', then the time) > Hm, you did forget the simplest way - to use unixepoch format. And this is very compact too. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross-database time function.
On Dec 28, 2009, at 9:29 AM, Simon Slavin wrote: On 28 Dec 2009, at 5:06pm, Israel Brewster wrote: Or perhaps some other way to extract the time of a timestamp that works in all three databases? Sorry. Each database handles time/date information differently (there's no SQL standard for doing it). You will fail to find one function that returns a useful result in all three SQL engines. I recommend that you use an agnostic way of storing your datestamps in the database, for instance as a TEXT field in the format mmdd(if you need just a date) hhmmss (if you need just a time) mmddThhmmss (the date, then a 'T', then the time) All of these formats are easy to recognise and sort and index correctly. Write library routines in your software which converts from your programming language's date representation to and from this representation. This way you need write just one routine and that routine /will/ work for all three SQL engines. Thanks for the info - I was sort of afraid of that. Yeah, I can implement parsing in my program easily enough, I was just hoping to be able to write a one-line SQL statement that would give me the info I wanted (just the time in that case, although I do want both time and date stored). Oh well. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cross-database time function.
On 28 Dec 2009, at 5:06pm, Israel Brewster wrote: > Or perhaps > some other way to extract the time of a timestamp that works in all > three databases? Sorry. Each database handles time/date information differently (there's no SQL standard for doing it). You will fail to find one function that returns a useful result in all three SQL engines. I recommend that you use an agnostic way of storing your datestamps in the database, for instance as a TEXT field in the format mmdd(if you need just a date) hhmmss (if you need just a time) mmddThhmmss (the date, then a 'T', then the time) All of these formats are easy to recognise and sort and index correctly. Write library routines in your software which converts from your programming language's date representation to and from this representation. This way you need write just one routine and that routine /will/ work for all three SQL engines. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cross-database time function.
I am looking for a cross-database (SQLite, PostgreSQL, MySQL) method of extracting the time portion of a timestamp. I can't just use the "time()" function, because in PostgreSQL it needs to be in quotes (or preceded by pg_catalog.) to work, while in SQLite quoting the function returns an error. It was suggested that I use a cast(timestamp_column as time) function, but while this works in PosgreSQL, in SQLite it returns the year of the timestamp, not the time. Is there a way to make the cast function actually return the time in SQLite? Or perhaps some other way to extract the time of a timestamp that works in all three databases? Thanks. --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
