php help
Jetpak is Public
Created By: fluff
Last Modified: 07/17/06
Summary: for php


Email Jetpak

Subscribe


Jetpak Tags:
code examples php

sql date query

mysql> SELECT * FROM test WHERE ts>=FROM_UNIXTIME(0);

From: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

update date

If you need to update data in a table that has a TIMESTAMP field which is auto updating, simply set the field equal to itself.

This is very handy if you happen to alter a table and add a new field and then wish to set that field to something, normally this would cause the TIMESTAMP field to be updated to the current timestamp, however if your timestamp data needs to remain intact (i.e. maintain the original update timestamp) then you'll want to preserve that data.

e.g.

UPDATE table_name SET my_timestamp=my_timestamp, new_data='something new' ;

From: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

sql date commands

  • CURDATE()

    Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. 

    mysql> SELECT CURDATE();
        -> '1997-12-15'
mysql> SELECT CURDATE() + 0;
        -> 19971215

  • CURRENT_DATE, CURRENT_DATE()

    CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

  • CURTIME()

    Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. 

    mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026

    • From:     http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

    timestamp to date

    function timestamp_db_php($date) 
    {
            $year   = substr($date,0,4);
            $month  = substr($date,4,2);
            $day    = substr($date,6,2);
            $hour   = substr($date,8,2);
            $minute = substr($date,10,2);
            $second = substr($date,12,2);
            //$epoch = date("U", mktime($time[0],$time[1],$time[2],$datebreak[1],$datebreak[2],$datebreak[0])); 
              $datetime = $month."-".$day."-".$year."  ".$hour.":".$minute.":".$second." ";
          return     $datetime; 
    } 
    ?>     Example timestamp_db_php("20021215153817"); would return "12-15-2002  15:38:17".

    From: http://www.zend.com/codex.php?id=1110&single=1

    db to from, form to db


          /**
      *  Copyright (c) 2001, Urs Gehrig 
      *  check for correct date format string, that comes from a form field.
      *  specify the delimiter.
      *
      *  the format 20010131 will be sortable within the database!
      */
          function form_to_db($date, $delimiter="." ) {
          if (    ereg ("([0-9]{1,2})$delimiter([0-9]{1,2})$delimiter([0-9]{2,4})", $date, $regs)) {
              if(    strlen($regs[1]) <2) $regs[1] = "0$regs[1]";
              if(    strlen($regs[2]) <2) $regs[2] = "0$regs[2]";
              if(    strlen($regs[3]) <4) $regs[3] = "20$regs[3]"; // this will work for the next 998 years
                  return "$regs[3]$regs[2]$regs[1]";
          } else {
              return     FALSE;
          }
      }

          /**
      *  Copyright (c) 2001, Urs Gehrig 
      *  put the db format 20010131 into the userfriendly format 31-01-2001 by
      *  passing the db value and the delimiter to the function.
      */
          function db_to_form($date, $delimiter="." ) {
              $d = array();
              $d[day] = substr($date, 6, 2);
              $d[month] = substr($date, 4, 2);
              $d[year] = substr($date, 0, 4);
          return     $d[day].$delimiter.$d[month].$delimiter.$d[year];
      }

    ?>

      /**
      *  Copyright (c) 2001, Urs Gehrig 
      *  Examples
      */
    ��     $date_from_form = "3-08-02";
          $date_from_db = "20020803";

      echo     "As it comes from the form field ($date_from_form) -> " . form_to_db($date_from_form, "-") . "
    "    ;
      echo     "As it goes to the browser ($date_from_db) -> " . db_to_form($date_from_db, ".");

    ?>

    From: http://www.zend.com/codex.php?id=454&single=1

    date formatting

    $mydate = date('F j, Y' strtotime($date));  // "January 2, 2006" format

    From: http://www.webdeveloper.com/forum/showthread.php?threadid=113738

    convert sql datetime

    easy way - to convert a "datetime" form mySQL into php date format....
    first - get the array form mySQL, then use

    date("d M Y H:i:s", strtotime($your_row['lastlogin_date']))
    ?>

    strtotime - easy converts a datetime timestamp into time  ( time() or date("U")).

    From: http://us3.php.net/date

    fuzzydate

    function fuzzyDate($timestamp)
    {
      if($timestamp > time())
         //we don't handle future dates
         return date('Y-m-d H:i', $timestamp);
      elseif($timestamp > mktime(0,0,0))
         //since midnight so it's today
         return 'Today '.date('H:i', $timestamp);
      elseif($timestamp > mktime(0,0,0) - 86400)
         //since midnight 1 day ago so it's yesterday
         return 'Yesterday '.date('H:i', $timestamp);
      elseif($timestamp > mktime(0,0,0) - 86400*7)
         //since midnight 7 days ago so it's this week
         return date('l H:i', $timestamp);
      elseif($timestamp > mktime(0,0,0,1,1))
         //since 1st Jan so it's this year
         return date('F j', $timestamp);
      else
         //ages ago!
         return date('F Y', $timestamp);
    }

    From: http://us3.php.net/date

    date

    $date = date('j F Y G', strtotime($date))

    From: http://forum.textdrive.com/viewtopic.php?pid=94459

    Link: mysql help

    Link: webmonkey php tutorials

    Summary: good




    ADVERTISING