python – How to send a user a message about the number of days spent in the bot when clicking the button

Question:

The bottom line is there is a telegram bot script

@bot.message_handler(commands=["start"])
def start(message):
keyboard = types.ReplyKeyboardMarkup(resize_keyboard=True, row_width=2)
keyboard.add(*[types.KeyboardButton(name) for name in ['Старт']])
bot.send_message(message.chat.id, 'Добро пожаловать\n\n', reply_markup=keyboard, parse_mode="Html", disable_web_page_preview=True)
AddUser (message.chat.id, message.chat.username, message.chat.first_name, message.chat.last_name, now)

Have a SQLite database

def AddUser(user_id, user_login, user_name, second_name, clock):

# Подключаемся к SQLite
conn = sqlite3.connect('user.db')
c = conn.cursor()

# Обработка SQL исключений
try:
    # Выполняем SQL запрос
    c.execute("INSERT INTO users (user_id, user_login, user_name, second_name, clock) VALUES (?, ?, ?, ?, ?);", (user_id, user_login, user_name, second_name, clock))
except sqlite3.DatabaseError as error:
    # В случаи ошибки
    print("Error:", error)

# Завершаем транзикцию
conn.commit()
# Закрываем соединение
conn.close()

And actually the button itself, when pressed, the user should see how many days have already passed since the first launch

@bot.message_handler(content_types=["text"])
def key(message):

    if message.text == 'Кол-во дней':
 bot.send_message(message.chat.id, 'Ваше время проведенное в боте "..." д. \n\n')

How to unite all this business with this code, so that each user would receive exactly his time?

select julianday('now') - julianday(min(clock)) as days
from tab where user_id=12345;

Answer:

DB entry:

# обратите внимание на последний параметр
# message.date - в формате unix timestamp
AddUser (message.chat.id, message.chat.username, message.chat.first_name, message.chat.last_name, message.date)

Getting date from DB and calculating delta in days from current date

@bot.message_handler(content_types=["text"])
def key(message):

    if message.text == 'Кол-во дней':
        get_date_with_bd = get_time_user(12345)
        bot.send_message(
            message.chat.id, 
            'Ваше время проведенное в боте {0} д. \n\n'.format(
                (datetime.datetime.now() - get_date_with_bd).days
            )
        )

def get_time_user(user_id):    
    conn = sqlite3.connect('user.db')
    c = conn.cursor()      
    get_timestamp = c.execute("select clock from users where user_id = (?)", (user_id,))
    get_timestamp = count.fetchall()
    db.close() 
    return get_timestamp[0][0]
Post Views: 0
Scroll to Top