Funciones de fecha y hora

• Ultima modificación: Mar 6, 2024 | YouTube

Preparacion

import sqlite3

from IPython.core.magic import register_cell_magic, register_line_magic

conn = sqlite3.connect(":memory:")
cur = conn.cursor()


@register_line_magic
def sql(line):
    return cur.execute(line).fetchall()

Funciones

##
## date()
##
%sql SELECT date()

[('2023-04-01',)]

##
## time()
##
%sql SELECT time()

[('17:12:29',)]

##
## datetime()
##
%sql SELECT datetime()

[('2023-04-01 17:12:29',)]

##
## strftime(format, date)
##
##      Caracteres de formato                    Valores de tiempo
##     ---------------------------------------------------------------------
##      %d  day of month: 00                     YYYY-MM-DD
##      %f  fractional seconds: SS.SSS           YYYY-MM-DD HH:MM
##      %H  hour: 00-24                          YYYY-MM-DD HH:MM:SS
##      %j  day of year: 001-366                 YYYY-MM-DD HH:MM:SS.SSS
##      %J  Julian day number (fractional)       YYYY-MM-DDTHH:MM
##      %m  month: 01-12                         YYYY-MM-DDTHH:MM:SS
##      %M  minute: 00-59                        YYYY-MM-DDTHH:MM:SS.SSS
##      %s  seconds since 1970-01-01             HH:MM
##      %S  seconds: 00-59                       HH:MM:SS
##      %w  day of week 0-6 with Sunday==0       HH:MM:SS.SSS
##      %W  week of year: 00-53                  now
##      %Y  year: 0000-9999                      DDDDDDDDDD
##      %%  %
##
%sql SELECT strftime('%Y', 'now')

[('2023',)]

%sql SELECT strftime('%Y', '2023-02-01')

[('2023',)]

%sql SELECT strftime('%w', '2023-02-01')

[('3',)]

%sql SELECT strftime('%Y', datetime())

[('2023',)]

##
## Modificadores
##
##  NNN days
##  NNN hours
##  NNN minutes
##  NNN.NNNN seconds
##  NNN months
##  NNN years
##  start of month
##  start of year
##  start of day
##  weekday N
##  unixepoch
##  julianday
##  auto
##  localtime
##  utc
##
%sql SELECT date('now', 'start of month')

[('2023-04-01',)]

##
## Cálculo del último dia del mes
##
%sql SELECT date('now', 'start of month', '+1 month', '-1 day')

[('2023-04-30',)]