Icon

kn_​example_​db_​sqlite_​timestamp

Play around with SQLite and Date and Time functions

Play around with SQLite and Date and Time functions



%d day of month: 00%f fractional seconds: SS.SSS%H hour: 00-24%j day of year: 001-366%J Julian day number%m month: 01-12%M minute: 00-59%s seconds since 1970-01-01%S seconds: 00-59%w day of week 0-6 with Sunday==0%W week of year: 00-53%Y year: 0000-9999%% % Play around with SQLite and Date and Time functionshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_db_sqlite_timestamp CAST(strftime('%s', strftime('%Y-%m-%dT00:00:00+00:00', <your_datetime_column>), 'unixepoch') as datetime)https://stackoverflow.com/questions/27545543/how-can-i-convert-a-datetime-string-to-a-unix-timestamp-in-sqlite3 localtime might include daylight saving time ... PRAGMA table_info(table_name); create tablemy_date_timeyyyy-MM-dd'T'HH:mm[:ss[.SSS]]DROP TABLE IF EXISTS `default`.`my_table_01`;my_table_01create database_01.sqlitemy_date_timejoin($my_date$,"T",$my_time$)my_dateyyyy-MM-ddmy_timeHH:mm[:ss[.SSS]]my_table_01sqlite_masterSELECT * FROM my_table_05my_table_05my_Timestamp DATETIME DEFAULT CURRENT_TIMESTAMPmy_table_05my_table_05INSERT INTO `my_table_05` VALUES (1, '2016-01-01 10:20:05.123') , (2, '2017-12-01 14:20:05.123');SELECT * FROM my_table_05my_table_00my_table_00strftime('%Y-%m-%d %H:%M', datetime(my_date_time/1000, 'unixepoch', 'localtime')) AS my_date_time_2datetime(my_date_time_2,'localtime') AS my_date_time_3my_table_01my_table_03my_table_02my_table_03my_table_03my_table_01DROP TABLE IF EXISTS `default`.`my_table_02`;SELECT * FROM PRAGMA_TABLE_INFO('my_table_03');SELECT * FROM PRAGMA_TABLE_INFO('my_table_01');SELECT * FROM PRAGMA_TABLE_INFO('my_table_02');my_table_01my_table_03 Table Creator String to Date&Time DB SQL Executor DB Writer SQLite Connector String Manipulation String to Date&Time String to Date&Time DB Reader DB Query Reader DB Query Reader DB SQL Executor DB Table Remover DB SQL Executor DB Query Reader DB Writer DB Reader DB Query DB Query DB Reader DB Query Reader DB ConnectionTable Writer DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Query Reader DB Query Reader DB Query Reader DB Table Creator DB Reader %d day of month: 00%f fractional seconds: SS.SSS%H hour: 00-24%j day of year: 001-366%J Julian day number%m month: 01-12%M minute: 00-59%s seconds since 1970-01-01%S seconds: 00-59%w day of week 0-6 with Sunday==0%W week of year: 00-53%Y year: 0000-9999%% % Play around with SQLite and Date and Time functionshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_db_sqlite_timestamp CAST(strftime('%s', strftime('%Y-%m-%dT00:00:00+00:00', <your_datetime_column>), 'unixepoch') as datetime)https://stackoverflow.com/questions/27545543/how-can-i-convert-a-datetime-string-to-a-unix-timestamp-in-sqlite3 localtime might include daylight saving time ... PRAGMA table_info(table_name); create tablemy_date_timeyyyy-MM-dd'T'HH:mm[:ss[.SSS]]DROP TABLE IF EXISTS `default`.`my_table_01`;my_table_01create database_01.sqlitemy_date_timejoin($my_date$,"T",$my_time$)my_dateyyyy-MM-ddmy_timeHH:mm[:ss[.SSS]]my_table_01sqlite_masterSELECT * FROM my_table_05my_table_05my_Timestamp DATETIME DEFAULT CURRENT_TIMESTAMPmy_table_05my_table_05INSERT INTO `my_table_05` VALUES (1, '2016-01-01 10:20:05.123') , (2, '2017-12-01 14:20:05.123');SELECT * FROM my_table_05my_table_00my_table_00strftime('%Y-%m-%d %H:%M', datetime(my_date_time/1000, 'unixepoch', 'localtime')) AS my_date_time_2datetime(my_date_time_2,'localtime') AS my_date_time_3my_table_01my_table_03my_table_02my_table_03my_table_03my_table_01DROP TABLE IF EXISTS `default`.`my_table_02`;SELECT * FROM PRAGMA_TABLE_INFO('my_table_03');SELECT * FROM PRAGMA_TABLE_INFO('my_table_01');SELECT * FROM PRAGMA_TABLE_INFO('my_table_02');my_table_01my_table_03Table Creator String to Date&Time DB SQL Executor DB Writer SQLite Connector String Manipulation String to Date&Time String to Date&Time DB Reader DB Query Reader DB Query Reader DB SQL Executor DB Table Remover DB SQL Executor DB Query Reader DB Writer DB Reader DB Query DB Query DB Reader DB Query Reader DB ConnectionTable Writer DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Query Reader DB Query Reader DB Query Reader DB Table Creator DB Reader

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2020-11-20
On NodePit since: 2020-11-23
Last update: 2024-05-02
Created with KNIME version: v4.7.0
Tags: sqldatetimedatabaseSQLite

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!