Datetime control in sqlite

最新推荐文章于 2025-09-05 21:27:49 发布

转载最新推荐文章于 2025-09-05 21:27:49 发布 · 59 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/vincentcuhk/blog/749230

文章标签：

#数据库 #python

本文介绍如何使用SQLite进行日期和时间的格式化操作，包括获取当前时间、添加小时数及天数等，并创建了一个视图来方便地查询这些时间点。

2019独角兽企业重金招聘Python工程师标准>>>

now:
select strftime('%d/%m/%Y %H:%M', 'now','localtime') as curr_time;

add 4 hours:
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+4 hours')) as curr_add_4_hrs;

lazy method, no need remember the syntax in everytime.

create view view_dt as 
select 
strftime('%d/%m/%Y %H:%M', 'now','localtime') as curr_time,
strftime('%M', 'now','localtime') as current_mins,
strftime('%H', 'now','localtime') as current_hr,
strftime('%d', 'now','localtime') as current_day,
strftime('%m', 'now','localtime') as current_month,
strftime('%Y', 'now','localtime') as current_yr,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+1 hours')) as curr_add_1_hr,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+2 hours')) as curr_add_2_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+3 hours')) as curr_add_3_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+4 hours')) as curr_add_4_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+5 hours')) as curr_add_5_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+6 hours')) as curr_add_6_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+7 hours')) as curr_add_7_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+8 hours')) as curr_add_8_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+9 hours')) as curr_add_9_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+10 hours')) as curr_add_10_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+11 hours')) as curr_add_11_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+12 hours')) as curr_add_12_hrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+1 day')) as curr_add_1_day,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+7 days')) as curr_add_7_days,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+1 month')) as curr_add_1_month,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+3 months')) as curr_add_3_months,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+6 months')) as curr_add_6_months,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+1 year')) as curr_add_1_yr,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+5 years')) as curr_add_5_yrs,
strftime('%d/%m/%Y %H:%M',datetime('now','localtime','+10 years')) as curr_add_10_yrs,
'view sqllite date time' as description;

next time need to use,query:
select curr_add_8_hrs as expire_time from view_dt;


ps: current_time -> sqlite reserved words
select strftime('%d/%m/%Y %H:%M', 'now','localtime') as current_time; -> X

转载于:https://my.oschina.net/vincentcuhk/blog/749230