Here&#39;s an example of how to **convert `DATETIME` to UNIX timestamp**:   
`SELECT UNIX_TIMESTAMP(STR_TO_DATE(&#39;Apr 15 2012 12:00AM&#39;, &#39;%M %d %Y %h:%i%p&#39;))`


Here&#39;s an example of how to **change date format**:  
`SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(&#39;Apr 15 2012 12:00AM&#39;, &#39;%M %d %Y %h:%i%p&#39;)),&#39;%m-%d-%Y %h:%i:%p&#39;)`


**Documentation:** [`UNIX_TIMESTAMP`][1], [`FROM_UNIXTIME`][2]


  [1]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp
  [2]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

You will certainly have to use both [`STR_TO_DATE`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) to convert your date to a MySQL standard date format, and  [`UNIX_TIMESTAMP`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp) to get the timestamp from it.

Given the format of your date, something like 

    UNIX_TIMESTAMP(STR_TO_DATE(Sales.SalesDate, &#39;%M %e %Y %h:%i%p&#39;))

Will gives you a valid timestamp. Look the [`STR_TO_DATE`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) documentation to have more information on the format string.

For current date just use `UNIX_TIMESTAMP()` in your MySQL query.

From http://www.epochconverter.com/

    SELECT DATEDIFF(s, &#39;1970-01-01 00:00:00&#39;, GETUTCDATE())
    
    My bad, SELECT unix_timestamp(time) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD. More on using timestamps with MySQL:
    
    http://www.epochconverter.com/programming/mysql-from-unixtime.php

I have been reading a lot of docs, and I&#39;m getting more and more confused.
I basically can&#39;t figure out the difference between a 

 * service
 * directive 
 * module

I see a lot of custom components. Sometimes they&#39;re using directives, sometimes services. It always starts with a module. Can someone explain with an example what the difference is between these three types?


What is the difference between service, directive and module?

I have a database table containing dates 

     (`date` timestamp NOT NULL DEFAULT &#39;0000-00-00 00:00:00&#39;). 

I&#39;m using MySQL. From the program sometimes data is passed without the date to the database. So, the date value is auto assigned to `0000-00-00 00:00:00`
when the table data is called with the date column it gives error

    ...&#39;0000-00-00 00:00:00&#39; can not be represented as java.sql.Timestamp.......

I tried to pass null value to the date when inserting data, but it gets assign to the current time.

Is there any way I can get the `ResultSet` without changing the table structure?

&#39;0000-00-00 00:00:00&#39; can not be represented as java.sql.Timestamp error

How do I convert the following format to unix timestamp?

    Apr 15 2012 12:00AM

The format I get from DB seems to have `AM` at the end. 
I&#39;ve tried using the following but it did not work:

    CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE,  
    CONVERT(TIMESTAMP, Sales.SalesDate, 103) AS TSSALESDATE

    where Sales.SalesDate value is Apr 15 2012 12:00AM

MySQL convert date string to Unix timestamp

How do I convert the following format to unix timestamp?
<pre><code class="hljs language-yaml">Apr 15 2012 12:00AM
</code></pre>
The format I get from DB seems to have <code>AM</code> at the end.
I've tried using the following but it did not work:
<pre><code class="hljs language-scss">CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE, 
CONVERT(TIMESTAMP, Sales.SalesDate, 103) AS TSSALESDATE

where Sales.SalesDate value is Apr 15 2012 12:00AM
</code></pre>

I have a CSV file sent to me in CSV. The field of interest is 8 digits. Some of those started with a 0. The field was sent numeric. So, I now have dropped some leading zeros.

I already converted the field to varchar. I now need to do this:

I have this now:

    12345678
    1234567

I need to have this:

    12345678
    01234567

Adding a leading zero to some values in column in MySQL

I was designing a database for a site where I need to use a boolean datetype to store only 2 states, true or false. I am using MySQL.  
While designing the database using phpMyAdmin, I found that I have both the BOOLEAN datatype and the TINYINT datatype.  
I went through different articles, some said TINYINT is the same as BOOLEAN, no difference. Some say BOOLEAN is converted into TINYINT in MySQL.

MY question is, if they both are same why do there exist two? There should be only one of them.

Here is the reference to the articles I read:  
http://www.careerride.com/MySQL-BOOL-TINYINT-BIT.aspx  
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

BOOLEAN or TINYINT confusion

I would like to know how can I output a number with 2 decimal places, without rounding the original number.

For example:

    2229,999 -&gt; 2229,99

I already tried:

    FORMAT(2229.999, 2)
    CONVERT(2229.999, DECIMAL(4,2))

Format number to 2 decimal places

I&#39;ve installed MySQL server on a remote Ubuntu machine. The `root` user is defined in the `mysql.user` table this way:

	mysql&gt; SELECT host, user, password FROM user WHERE user = &#39;root&#39;;
	+------------------+------+-------------------------------------------+
	| host             | user | password                                  |
	+------------------+------+-------------------------------------------+
	| localhost        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
	| ip-10-48-110-188 | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
	| 127.0.0.1        | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
	| ::1              | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
	+------------------+------+-------------------------------------------+

I can access with user `root` from the same remote machine command-line interface using the standard `mysql` client. Now I want to **allow root access from every host on the internet**, so I tried adding following row (it&#39;s an exact duplicate of the first row from previous dump, except for the `host` column):

	mysql&gt; SELECT host, user, password FROM user WHERE host = &#39;%&#39;;
	+------------------+------+-------------------------------------------+
	| host             | user | password                                  |
	+------------------+------+-------------------------------------------+
	| %                | root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
	+------------------+------+-------------------------------------------+

But my client on my personal PC continues to tell me (I obscured the server IP):

&gt; SQL Error (2003): Can&#39;t connect to MySQL server on &#39;46.x.x.x&#39; (10061)

I can&#39;t tell if it&#39;s a authentication error or a network error. On the server firewall I enabled port *3306/TCP* for *0.0.0.0/0*, and that&#39;s ok for me...

MySQL root access from all hosts

I am importing a fairly large database. The `.sql` file has almost 1,000,000 lines in it. Problem is that I am getting a syntax error when trying to import the database. It says: 

&gt; ERROR 1064 (42000) at line 8428420: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;&lt;br /&gt;
&lt;b&gt;Fatal error&lt;/b&gt;:  Maximum execution time of 600 seconds exceeded in &lt;b&#39; at line 194

Normally I&#39;d just open the .sql file and fix the error. But my computer is really struggling to open this file. 

Is there any way I can ignore errors when importing a MySQL database?

MySQL: ignore errors when importing?

I have the following code in my C# program.

    DateTime dateForButton =  DateTime.Now;  
    dateForButton = dateForButton.AddDays(-1);  // ERROR: un-representable DateTime

Whenever I run it, I get the following error:

&gt; _The added or subtracted value results in an un-representable DateTime._&lt;br/&gt;
&gt; _Parameter name: value_

Iv&#39;e never seen this error message before, and don&#39;t understand why I&#39;m seeing it.  From the answers Iv&#39;e read so far, I&#39;m lead to believe that I can use -1 in an add operation to subtract days, but as my question shows this is not the case for what I&#39;m attempting to do.


Subtract days from a DateTime

How can I convert Persian date to Gregorian date using System.globalization.PersianCalendar?
Please note that I want to convert my Persian Date (e.g. today is 1391/04/07) and get the Gregorian Date result which will be 06/27/2012 in this case.
I&#39;m counting seconds for an answer ...

Convert Date from Persian to Gregorian

I am using TimeStamp class to convert seconds into Day,Hours,Minutes,Seconds. I used following code

    public static void calculateTime(long seconds) {
    		int day = (int)TimeUnit.SECONDS.toDays(seconds);		
    		long hours = TimeUnit.SECONDS.toHours(seconds) - TimeUnit.SECONDS.toHours(TimeUnit.SECONDS.toDays(seconds));
    		long minute = TimeUnit.SECONDS.toMinutes(seconds) - TimeUnit.SECONDS.toMinutes(TimeUnit.SECONDS.toHours(seconds));
    		long second = TimeUnit.SECONDS.toSeconds(seconds) - TimeUnit.SECONDS.toSeconds(TimeUnit.SECONDS.toMinutes(seconds));
    		
    		System.out.println(&quot;Day &quot; + day + &quot; Hour &quot; + hours + &quot; Minute &quot; + minute + &quot; Seconds &quot; + second);
    		
    	}

But I am not getting right result. 
For example when I called this method as `calculateTime(3600)` it gives me the result as `Day 0 Hour 1 Minute 60 Seconds 3540` instead of `Day 0 Hour 1 Minute 0 Seconds 0`. 


*What is the wrong with my logic? Please help me.* 

Java: convert seconds into day, hour, minute and seconds using TimeUnit

I currently have php returning the current date/time like so: 

    $now = date(&quot;Y-m-d H:m:s&quot;);

What I&#39;d like to do is have a new variable `$new_time` equal `$now + $hours`, where `$hours` is a number of hours ranging from 24 to 800. 

Any suggestions? 



Add &#39;x&#39; number of hours to date

The following function produces today&#39;s date; how can I make it produce only yesterday&#39;s date?

    private String toDate() {
            DateFormat dateFormat = new SimpleDateFormat(&quot;yyyy/MM/dd HH:mm:ss&quot;);
            Date date = new Date();    
            return dateFormat.format(date).toString();
    }

This is the output:

    2012-07-10

I only need yesterday&#39;s date like below. Is it possible to do this in my function?

    2012-07-09

Get yesterday&#39;s date using Date

How do I get the Scala UTC timestamp in seconds since January 1st, 1970?

Scala UTC timestamp in seconds since January 1st, 1970

I would like to get the current timestamp and print it out using `fprintf`.

How do I get the unix timestamp in C as an int?

How do I convert &#39;timeStamp&#39; to `date` after I get the count in java?

My current code is as follows:

    public class GetCurrentDateTime {
    
        public int data() {
            int count = 0;
            java.sql.Timestamp timeStamp = new Timestamp(System.currentTimeMillis());
            java.sql.Date date = new java.sql.Date(timeStamp.getTime()); 
			System.out.println(date);
            //count++;
    
            try {
                Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
                Connection con = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/pro&quot;, &quot;root&quot;, &quot;&quot;);
    
                PreparedStatement statement = con.prepareStatement(&quot;select * from orders where status=&#39;Q&#39; AND date=CURDATE()&quot;);
                ResultSet result = statement.executeQuery();
                while (result.next()) {
                    // Do something with the row returned.
                    count++; //if the first col is a count.
                }
            } catch (Exception exc) {
                System.out.println(exc.getMessage());
            }

            return count;
        }
    }

This is my database:
![enter image description here][1]

  [1]: http://i.stack.imgur.com/4wXsC.png

Here the output I got was 2012-08-07 0, but the equivalent query returns 3. Why do I get 0?

How to convert TimeStamp to Date in Java?

I am trying to convert time-stamps of the format &quot;2012-07-24T23:14:29-07:00&quot; 
to datetime objects in python using strptime method. The problem is with the time offset at the end(-07:00). Without the offset i can successfully do

    time_str = &quot;2012-07-24T23:14:29&quot;
    
    time_obj=datetime.datetime.strptime(time_str,&#39;%Y-%m-%dT%H:%M:%S&#39;)

But with the offset i tried

    time_str = &quot;2012-07-24T23:14:29-07:00&quot;
    
    time_obj=datetime.datetime.strptime(time_str,&#39;%Y-%m-%dT%H:%M:%S-%z&#39;).

But it gives a Value error saying &quot;z&quot; is a bad directive.

Any ideas for a work around?





Convert timestamps with offset to datetime obj using strptime

How would I go about getting a timestamp in php for today at midnight. Say it&#39;s monday 5PM and I want the Timestamp for Monday(today) at midnight(12 am) which already has happened.

Thank you

Getting a timestamp for today at midnight?

How can I convert normal date `2012.08.10` to unix timestamp in javascript?

Fiddle: http://jsfiddle.net/J2pWj/
&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
&lt;br /&gt;
I&#39;ve seen many posts here that convert it in PHP, Ruby, etc... But I need to do this inside JS.

Convert normal date to unix timestamp

If my Unix machine is set to IST timezone, how can I get the current GMT time?

How do I get GMT time in Unix?

I am making a countdown timer for an event page, i used moment js for this.  

Here is [fiddle][1] for this.

I am calculating date difference between event date and current date (timestamp), then using &quot;duration&quot; method from moment js.  But the time left is not coming as expected.  
**Expected** - 00:30m:00s  
**Actual** - 5h:59m:00s  

Code :   
 

    &lt;script&gt;
      $(document).ready(function(){
    
        var eventTime = &#39;1366549200&#39;;
        var currentTime = &#39;1366547400&#39;;
        var time = eventTime - currentTime;
        var duration = moment.duration(time*1000, &#39;milliseconds&#39;);
        var interval = 1000;
    
        setInterval(function(){
          duration = moment.duration(duration.asMilliseconds() - interval, &#39;milliseconds&#39;);
          $(&#39;.countdown&#39;).text(moment(duration.asMilliseconds()).format(&#39;H[h]:mm[m]:ss[s]&#39;));
        }, interval);
      });
      &lt;/script&gt;

I read the momentjs documentation to figure out the problem, but no luck.

Thanks for your time.


**Update :**    

I end up doing like this :   
 
    &lt;script&gt;
      $(document).ready(function(){
    
        var eventTime = &#39;1366549200&#39;;
        var currentTime = &#39;1366547400&#39;;
        var leftTime = eventTime - currentTime;//Now i am passing the left time from controller itself which handles timezone stuff (UTC), just to simply question i used harcoded values.
        var duration = moment.duration(leftTime, &#39;seconds&#39;);
        var interval = 1000;
    
        setInterval(function(){
          // Time Out check
          if (duration.asSeconds() &lt;= 0) {
            clearInterval(intervalId);
            window.location.reload(true); #skip the cache and reload the page from the server
          }

          //Otherwise
          duration = moment.duration(duration.asSeconds() - 1, &#39;seconds&#39;);
          $(&#39;.countdown&#39;).text(duration.days() + &#39;d:&#39; + duration.hours()+ &#39;h:&#39; + duration.minutes()+ &#39;m:&#39; + duration.seconds() + &#39;s&#39;);
        }, interval);
      });
      &lt;/script&gt;

[JS Fiddle][2].


  [1]: http://jsfiddle.net/wt812nfo/
  [2]: http://jsfiddle.net/arpit/cxyms

Countdown timer using Moment js

Regarding Unix (POSIX) time, [Wikipedia][1] says: 

&gt; Due to its handling of leap seconds, it is neither a linear representation of time nor a true representation of UTC.

But the Unix `date` command does not seem to be aware of them actually

&lt;!-- language: lang-bsh --&gt;

    $ date -d &#39;@867715199&#39; --utc
    Mon Jun 30 23:59:59 UTC 1997
    $ date -d &#39;@867715200&#39; --utc
    Tue Jul  1 00:00:00 UTC 1997

While there should be a leap second there at `Mon Jun 30 23:59:60 UTC 1997`.

Does this mean that only the `date` command ignores leap seconds, while the *concept* of Unix time doesn&#39;t?

  [1]: http://en.wikipedia.org/wiki/Unix_time

Unix time and leap seconds

Python provides different packages (`datetime`, `time`, `calendar`) as can be seen [here][1] in order to deal with time. I made a big mistake by using the following to get current GMT time `time.mktime(datetime.datetime.utcnow().timetuple())`

What is a simple way to get current GMT time in Unix timestamp?


  [1]: https://stackoverflow.com/questions/8542723/change-datetime-to-unix-time-stamp-in-python

Content Type	Original Author	Original Content on Stackoverflow
Question	redcoder	View Question on Stackoverflow
Solution 1 - Mysql	Query Master	View Answer on Stackoverflow
Solution 2 - Mysql	strnk	View Answer on Stackoverflow
Solution 3 - Mysql	stackMonk	View Answer on Stackoverflow
Solution 4 - Mysql	Brian Smith	View Answer on Stackoverflow

MySQL convert date string to Unix timestamp

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

'0000-00-00 00:00:00' can not be represented as java.sql.Timestamp error

What is the difference between service, directive and module?

Attributions