Cancel
Showing results for 
Search instead for 
Did you mean: 

Datetime format for SQLite

Legend
Legend

Hello,

 

I have a problem when using the SQLite interface. In my database I have a table called Orders with a column formatted as datetime which is always formatted to YYYY-MM-DD HH:MMSmiley FrustratedS.SSSS as the only option.

 

In Plant Simulation when trying to run a query to import this database into a table with

 

SQLite.sql(testTable, "SELECT * FROM Orders);

I run into a problem. Due to Plant Simulation only recognizing YYYY/MM/DD or YYYY.MM.DD as datetime formats the column in the Plant Simulation table is not automatically set to datetime. My understanding of the problem is that since Plant Simulation only supports certain formats and with '-' isn't one of them this is not possible?

 

Or am I misunderstanding this issue? Would it be possible to also add YYYY-MM-DD as a datetime format in Plant Simulation?

 

4 REPLIES

Re: Datetime format for SQLite

Siemens Phenom Siemens Phenom
Siemens Phenom

Hi verbalins,

 

you can use a select statement like the following:

select strftime('%d.%m.%Y %H:%M:%S', x) from test;

 

Here the strftime function of SQLite is used to convert the date stored in column 'x' in the table test to the format Plant Simulation understands:

 

Regards,

Ralf

Regards,

Ralf
------------------------------------------------------------------------------------------------------
Did you like the answer? Then click the Thumbs Up button.
Did the answer solve your problem? Then accept the answer as solution.

Re: Datetime format for SQLite

Legend
Legend

Thanks for the reply Ralf!

 

That was the solution I came up with ('%Y/%m/%d %H:%M:%S.%f').

 

But it’s not really optimal, since Plant Simulation doesn't recognize the format. Then I still will have to convert the column through setDataType(column, "DateTime"). Seeing as the other formats, like integer and text is possible to convert automatically with the sql method.

 

But this is something that won't be possible in Plant Simulation automtically? I have the same issue with locally formatted dates as well in Excel. They also use '-' instead of '/' or '.'. In Excel it's of course easier to change.

Re: Datetime format for SQLite

Siemens Phenom Siemens Phenom
Siemens Phenom

Right, you have to set the data type manually.

 

SQLite has no data type for dates. It can be stored in integer, float or text columns.

So there is no way for Plant Simulation to detect that it is a date column.

Regards,

Ralf
------------------------------------------------------------------------------------------------------
Did you like the answer? Then click the Thumbs Up button.
Did the answer solve your problem? Then accept the answer as solution.

Re: Datetime format for SQLite

Legend
Legend

Ah, now I see the reason why. Thank you very much Ralf!