MYSQL FROM_UNIXTIME, DATE_FORMAT, SQLITE3,

Avec sqlite3 j'utilise strftime('%w',unixtime,'unixepoch','localtime') pour obtenir le jour de la semaine sous forme numérique de 0 à 6

strftime('%w',unixtime,'unixepoch','localtime')

Avec mysql la commande équivalente est :

FROM_UNIXTIME(unixtime,'%w')

Pas besoin de préciser localtime, car la conversion se fait directement selon le timezone (par défaut SYSTEM)

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' orYYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.

mysql> SELECT FROM_UNIXTIME(1196440219);
-> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
-> 20071130103019.000000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
->                      '%Y %D %M %h:%i:%s %x');
-> '2007 30th November 10:30:59 2007'

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For details, see the description of the UNIX_TIMESTAMP() function.

• DATE_FORMAT(date,format)

  Formats the date value according to the format string.

  The following specifiers may be used in the format string. The “%” character is required before format specifier characters.

  Specifier	Description
  %a	Abbreviated weekday name (Sun..Sat)
  %b	Abbreviated month name (Jan..Dec)
  %c	Month, numeric (0..12)
  %D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
  %d	Day of the month, numeric (00..31)
  %e	Day of the month, numeric (0..31)
  %f	Microseconds (000000..999999)
  %H	Hour (00..23)
  %h	Hour (01..12)
  %I	Hour (01..12)
  %i	Minutes, numeric (00..59)
  %j	Day of year (001..366)
  %k	Hour (0..23)
  %l	Hour (1..12)
  %M	Month name (January..December)
  %m	Month, numeric (00..12)
  %p	AM or PM
  %r	Time, 12-hour (hh:mm:ss followed by AM or PM)
  %S	Seconds (00..59)
  %s	Seconds (00..59)
  %T	Time, 24-hour (hh:mm:ss)
  %U	Week (00..53), where Sunday is the first day of the week
  %u	Week (00..53), where Monday is the first day of the week
  %V	Week (01..53), where Sunday is the first day of the week; used with %X
  %v	Week (01..53), where Monday is the first day of the week; used with %x
  %W	Weekday name (Sunday..Saturday)
  %w	Day of the week (0=Sunday..6=Saturday)
  %X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
  %x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
  %Y	Year, numeric, four digits
  %y	Year, numeric (two digits)
  %%	A literal “%” character
  %x	x, for any “x” not listed above

  Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as '2014-00-00'.

  The language used for day and month names and abbreviations is controlled by the value of the lc_time_namessystem variable (Section 9.8, “MySQL Server Locale Support”).

  DATE_FORMAT() returns a string with a character set and collation given by character_set_connection andcollation_connection so that it can return month and weekday names containing non-ASCII characters.

  mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
  -> 'Sunday October 2009'
  mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
  -> '22:23:00'
  mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
  ->                 '%D %y %a %d %m %b %j');
  -> '4th 00 Thu 04 10 Oct 277'
  mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
  ->                 '%H %k %I %r %T %S %w');
  -> '22 22 10 10:23:00 PM 22:23:00 00 6'
  mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
  -> '1998 52'
  mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
  -> '00'
  

mysql variables

Pour lister les variables :

SHOW VARIABLES;