Topic: SQLite database CURRENT_TIMESTAMP

Hello. First of all congratulations on such a wonderful product! A great and lightweight alternative to MSAccess. I've been playing around with it all evening yesterday (downloaded the latest version).
I am having an issue with the database (SQLite, haven't tried MySQL yet).
I am creating a table with a field of DATETIME type and I tried to set a default value to CURRENT_TIMESTAMP.  This does not appear to work for some reason. A workaround I found is to use a hidden DateTimePicker object in a form, but it is not the correct way.
Am I doing something wrong?

Re: SQLite database CURRENT_TIMESTAMP

Hello.


Please download latest beta version, I made some change to work it properly.
https://www.dropbox.com/s/5x7vjreoju45z … a.zip?dl=0


Now you can set default value (SQLite):

(datetime('now','localtime'))

But you can set this non constant default value before database file will be created, so you must delete database file (sqlite.db) if you want set the default value for a field..

Dmitry.

Re: SQLite database CURRENT_TIMESTAMP

OK, I'll try.
Thanks for quick reply.

4 (edited by ykurchidi 2018-04-17 22:08:49)

Re: SQLite database CURRENT_TIMESTAMP

Your suggested method [(datetime('now','localtime'))] works with SQLite, but does not work with MySQL.
However, in the latest beta you provided CURRENT_TIMESTAMP indeed works fine (I've run just a few tests).
Thank you for your great support.
How do I mark this topic as SOLVED?

ADD: CURRENT_TIMESTAMP stores the date/time in GMT instead of local time. I've read that this is best practice and conversion to local time should be done during SELECT. I don't see if this can be done without scripting in MVD (yet?).
ADD2: According to W3Schools: "The CURRENT_TIMESTAMP(), LOCALTIME(), and LOCALTIMESTAMP() functions are synonyms for the NOW() function". If I understand correctly, this function should return local time instead of GMT, correct?

Re: SQLite database CURRENT_TIMESTAMP

How do I mark this topic as SOLVED?

There is no option for that.


CURRENT_TIMESTAMP: Returns the current date time with your timezone configured.


How to change time zone
https://www.inmotionhosting.com/support … -time-zone

Dmitry.

Re: SQLite database CURRENT_TIMESTAMP

OK. Thank you very much. I have MySQL on XAMPP, I'll play around when I have spare time.

I prefer using CURRENT_TIMESTAMP because MySQL and SQLite support it natively. Maybe the time zone should be set during sqlite.db creation in order for CURRENT_TIMESTAMP to work correctly?

Re: SQLite database CURRENT_TIMESTAMP

SQLite don't have option to set time zone, just use:

(datetime('now','localtime'))
Dmitry.