On 02 Nov 2017, at 12:23, Rajat Pal <rajat....@oracle.com> wrote:

> In SQLite database, All the values of datetime fields are saved as text
> without any format check. We can save any text value in the datetime column.
> This behavior is creating problem for us because for different regional
> settings,  datetime values are getting saved in different format.

As sqlite has no DATETIME field, what you actually get is a TEXT field in which 
you can store anything.

> For example, if the regional format is English(United States), the value is
> saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland),
> the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as
> time separator and some dates have dot (.) as time separator.

In whatever language you use to call sqlite, you should write a general 
function that can take anything that looks like a date/time and convert it to 
seconds since the epoch (or any other fixed historical moment), then store that 
value as an integer using sqlite. If you are using PHP, the strtotime() 
function does a very good job for that purpose.

Then when you retreive it, convert it to a time/date in the form that the local 
user expects.

Cheers  --  Tim
sqlite-users mailing list

Reply via email to