NOWWORKINGON

gpodder

select id, title , download_filename, published,  datetime( published, 'unixepoch' ),description
from episode
where 
-- podcast_id = 1  -- = AmRen
podcast_id = 2  -- = CCR
-- podcast_id = 3  -- = The Art of Manliness
-- and id = 62
and datetime( published, 'unixepoch' ) >= '2022-06-01'
and datetime( published, 'unixepoch' ) < '2022-07-01'
order by published desc

published : integer containing a datetime , zie sqllite data types

test :
id: 62
title : Counter-Currents Radio Podcast No. 448 The Writers’ Bloc with Karl Thorburn on Mutually Assured Destruction
download_filename : Writers-Bloc-with-Karl-Thorburn-May-22-2022.mp3
=>
1. rename the file , in gpodder download location
2. update filename in metaddata :

update episode
set download_filename = 'CCR-448_2022-05-22_The Writers’ Bloc with Karl Thorburn on Mutually Assured Destruction.mp3'  
where id = 62

update episode
set download_filename = 'AR_2022-08-16_with_GregJohson_Against the Current.mp3'
where id = 119

update episode
set download_filename = 'AR_2022-08-17_What’s the Most Dangerous Job in America? .mp3'
where id = 120  

update episode
set download_filename = 'AR_2022-08-24_Loomer Loses in a Nail-Biter.mp3' 
where id = 124  

update episode
set download_filename = 'CCR-475_2022-08-15_WB_with Karl Thorburn on Unlimited Power'   
where id = 117  

1. restart gpodder if it is opened ( there is a caching of some sort)
   deze test resultaat=OK

probleem 2 is echter : move renamed podcast file naar /mnt/data/library/ én tevens de meta bewaren
oplossing : sync de database met een database in /library
en periodiek de gPodder download folder opschonen

sqllite data types

Unix-style epoch-based timestamp, i.e. number of seconds since 1970-01-01 00:00

convert to a datetime :

select datetime( 1323648000, 'unixepoch' );
SELECT DATEADD(SS, 1323648000, '01/01/1970') : 

– returns version
select sqlite_version();

– getting started with sqlite & python
https://pynative.com/python-sqlite/

import

1/ via command line

# start a sqlite3 command session : 
 sqlite3

-- open/connect to  a database
.open /mnt/data/dev/projects/ ...   {db}.sqlite3

-- import csv file data into a non existing new {tablename} 
-- csv file  :  comma seperated , text delimitted with "
.import '/path/file.csv'   {tablename} 

2/ via sqlite studio,
- via methode 1/ : the csv file gets imported but all data gets into 1 single text column
- i do not find options (yet) to change that behaviour

with sqllite studio there is an import tool , but import is only possible with an existing table
you need to cretae that with thecoresponding fields & types