logo
down
shadow

how to check null or not if mysql datatype is timestamp with a default value of "0000-00-00 00:00"?


how to check null or not if mysql datatype is timestamp with a default value of "0000-00-00 00:00"?

By : user2951354
Date : November 18 2020, 01:01 AM
seems to work fine I think the best way for you is to use this method
code :
SELECT  IF(last_login = '0000-00-00 00:00:00', "null", "not null") as last_login from ...


Share : facebook icon twitter icon
mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00

mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00


By : user2282622
Date : March 29 2020, 07:55 AM
around this issue The MySQL SQL mode by default allows zero dates.
My belief is that the LOAD DATA INFILE command is reading the blank position intended to be a DATETIME, and automatically using a zero date before the insertion. This would explain why your default constraint isn't being applied - the value isn't null when being inserted.
What's the easiest way to check that a string is not null, not empty and not = "0000"?

What's the easiest way to check that a string is not null, not empty and not = "0000"?


By : user3905725
Date : March 29 2020, 07:55 AM
To fix the issue you can do I was using the following:
code :
!string.IsNullOrEmpty(Model.ProductID) && Model.ProductID != "0000"
public static class StringExtensions
{
    public static bool IsNullEmptyOrZeros(this string value)
    {
        return !string.IsNullOrEmpty(value) && value != "0000";
    }
}
if (!Model.ProductID.IsNullEmptyOrZeros())
{
    ...
}
ormlite: Dealing with a default Value of "0000-00-00" for a Date column in Mysql

ormlite: Dealing with a default Value of "0000-00-00" for a Date column in Mysql


By : Sana Syed
Date : March 29 2020, 07:55 AM
Any of those help The only way you can do this is with a custom persister. You can then control how ORMLite stores and retrieves data from the database. This feature is [unfortunately] not well documented.
You specify the class like this:
code :
@DatabaseField(persisterClass = MyDatePersister.class)
Date birthDate;
public class MyDatePersister extends com.j256.ormlite.field.types.BaseDataType {
    private static final MyDatePersister singleTon = new MyDatePersister();
    @SuppressWarnings("deprecation")
    private static final Timestamp ZERO_TIMESTAMP = new Timestamp(0, 0, 0, 0, 0, 0, 0);

    private MyDatePersister() {
        super(SqlType.DATE, new Class<?>[] { Date.class });
    }

    public static MyDatePersister getSingleton() {
        return singleTon;
    }

    @Override
    public Object resultToSqlArg(FieldType fieldType, DatabaseResults results,
            int columnPos) throws SQLException {
        Timestamp timestamp = results.getTimestamp(columnPos);
        if (timestamp == null || ZERO_TIMESTAMP.equals(timestamp)) {
            return null;
        } else {
            return timestamp;
        }
    }
}
Doctrine2 default timestamp wtih "0000-00-00 00:00:00" format?

Doctrine2 default timestamp wtih "0000-00-00 00:00:00" format?


By : user3640205
Date : March 29 2020, 07:55 AM
I wish this helpful for you Okay, I found a crazy workaround for this, but I honestly couldn't find a better solution. So, for anyone else stuck in the same mess I am, you're welcome lol.
First, the rules:
code :
/**
 * @var \DateTime 
 *
 * @ORM\Column(type="datetime")
 * @Gedmo\Timestampable(on="create")
 */
private $date_created;

/**
 * @var \DateTime 
 *
 * @ORM\Column(type="datetime")
 * @Gedmo\Timestampable(on="update")
 */
private $date_edited;

/**
 * @var \DateTime 
 *
 * @ORM\Column(type="datetime", options={"default":"0000-00-00 00:00:00"})
 */
private $date_sent;

/**
 * @var \DateTime 
 *
 * @ORM\Column(type="datetime", options={"default":"0000-00-00 00:00:00"})
 */
private $date_cancelled;

/**
 * @var \DateTime 
 *
 * @ORM\Column(type="datetime")
 * @Gedmo\Timestampable(on="change", field="requested_ship_date")
 */
private $requested_ship_date;


/*
 * Constructor
 */
public function __construct()
{
    if (!$this->date_sent) $this->date_sent = new DateTime("0000-00-00 00:00:00", new DateTimeZone("Zulu"));
    if (!$this->date_cancelled) $this->date_cancelled = new DateTime("0000-00-00 00:00:00", new DateTimeZone("Zulu"));
}

/**
 * Updates time sent 
 *
 * @return Order
 */
public function send()
{
    $this->date_sent = new DateTime("now");

    return $this;
}

/**
 * Updates time cancelled
 *
 * @return Order
 */
public function cancel()
{
    $this->date_cancelled = new DateTime("now");

    return $this;
}
MySQL column type "TIMESTAMP" implicitly includes "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_T

MySQL column type "TIMESTAMP" implicitly includes "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_T


By : Marc de Garis
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further In MySQL 5.6.5 there are several updates regarding this initialization, you can see on this link (Automatic Timestamp Properties Before MySQL 5.6.5).
If you're using MySQL <= 5.6.5, in order to ignore this initialization you need to set the DEFAULT value to 0 or NULL with NULL allowed.
Related Posts Related Posts :
  • New to PHP, trying to extract information from another website
  • JavaScript AmChart to Image for Email
  • Is javascript validation enough to keep my forms secure?
  • Regex extract variables from [shortcode]
  • Download a .mp3 link and save it to computer with PHP
  • Error with the ereg_replace
  • Getting the fields attached to a bundle of an entity in Drupal
  • Php how to check multiple times (within `foreach`) if values from array exists in mysql?
  • MySQL and PHP parsing strange string
  • Pull specific data from multidimensional associative array
  • How to get the original URI extension using PHP Tonic?
  • Upload videos and images from Android to PHP server?
  • Unset a multidimensional array with another multidimensional array with values than key
  • Calling a user e-mail to add to a mailto link PHP
  • Why is override_function coming back as an undefined function
  • PHP mySQLi_fetch_all: iterate through each row
  • Using pow in php
  • What's wrong with my PHP Script? T_String issue
  • how to call a function of another file without including that file
  • is_rgb function in php
  • transform the numbers to letters using php
  • How to catch an exception from another class method PHP
  • PHP exec is blocked by ISP
  • PHP4 to PHP5 migration character encoding. latin1 vs utf8
  • Begin Transaction not defined
  • How much harm can a user do with xss on a page unique to them?
  • How to import data from a php file to javascript variable
  • PHP and Javascript - log complete workflow
  • When I echo !!! 0 it gives me a strange result
  • jquery ajax post dies after submit
  • How to upload file with curl on sftp server
  • Advanced search using a text input to search through only one category
  • Why do I get "Non-static method App\Models\Category::products() should not be called statically" in laravel
  • Stop notices from undefined indexes in arrays
  • PHP Excel Error when entering SUM function to a cell
  • laravel 4 eloquent eager load relation count
  • click submit in html and run php code
  • Extremely frustrating behavior with Internet Explorer, Bootstrap and Respond.js
  • Vkontakte vtag issues
  • Divide the number to a correct decimal
  • mysqli_insert_query not working
  • How to make a sitemap using zend framework 1
  • Laravel: array to Model with relationship tree
  • $_SERVER['DOCUMENT_ROOT'] vs $path = $_SERVER['DOCUMENT_ROOT']
  • What is the best way to store multiple checkbox values in the database and work with it later on
  • Why does foreach copy the array when we did not modify it in the loop?
  • Receive Jquery/Ajax Post Request in PHP
  • How to better use the if(!empty()){ //codes} statement
  • PHP error mysql_close() boolean given
  • Move an image file from one server to another using PHP
  • PHP: Encoding non ISO-8859-1 charactrers into utf8
  • Handling MySQL errors in PHP
  • Object orientated mysql num_rows query in one line
  • Remove not closed html elements from end of text
  • Generate random ENUM value [PHP, MySQL]
  • Deleting data from table using jQuery and SQL
  • How to make sure the constructor is executed when I'm trying to reach a static property?
  • Trying to understand arrow next to PHP function
  • Form using external php file to show "Sucess"
  • WooCommerce - new shipping method - how to query individual product weights
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org