-1

I would like to convert the May 1, 2017 12:15:00 PM so that it reads like so 1493612100000

Then convert it into string. Is it possible in Java and MySQL and how to do it?

Coder
  • 1,917
  • 3
  • 17
  • 33
Electronic Circuit
  • 335
  • 5
  • 18
  • 1
    Parse the `String` to a `Date` or `LocalDateTime` object, get the "millis" value from it knock yourself out. Problem is, you will lose time zone context, but since there's no TZ in your format, that's probably already a problem :P – MadProgrammer Jan 27 '17 at 02:49
  • 1
    What is "May 1, 2017 12:15:00 PM"? Is this a `String`? `Date`? `LocalDateTime`? Is it a database column? What are you trying to do? – shmosel Jan 27 '17 at 02:50
  • 2
    You appear to have two basic problems, converting a date to a valid `Date` or `LocalDateTime` object, plenty of examples of that around; getting the millisecond value from said object, again, plenty of examples of that around. If you're not going to make some kind of effort to solve the problem, don't expect others to do the same. If try something and get stuck, we're here to help – MadProgrammer Jan 27 '17 at 02:53
  • It would help if we knew if you were starting with this date in Java or in MySQL. – Powerlord Jan 27 '17 at 04:32
  • On a side note, you can convert a Java `LocalDateTime` into a `java.sql.Timestamp`, which JDBC can use to set various types of Date fields in MySQL. – Powerlord Jan 27 '17 at 04:36

2 Answers2

1

You can do this in MySQL in two steps. First, convert the string timestamp to a datetime using STR_TO_DATE(), then convert that date to milliseconds since the epoch using UNIX_TIMESTAMP().

SELECT 1000*UNIX_TIMESTAMP(STR_TO_DATE(col, '%M %e, %Y %h:%i:%s %p')) AS millis
FROM yourTable

This will return milliseconds since the epoch, which is what your sample data is referring to.

Demo here:

SQLFiddle

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

What you mean 1493612100000? If it is only comparable integer you can use next solution.

Mysql have function UNIX_TIMESTAMP it may be used like this:

SELECT UNIX_TIMESTAMP(`table`.`date`) from `table` WHERE ...;

To convert your string to date use:

STR_TO_DATE('May 1, 2017 12:15:00 PM', '%M %d, %Y %h:%i:%s%p')

Together:

SELECT UNIX_TIMESTAMP( STR_TO_DATE( ... ) )
msagala25
  • 1,806
  • 2
  • 17
  • 24
oklas
  • 7,935
  • 2
  • 26
  • 42
  • *"I would like to convert the May 1, 2017 12:15:00 PM so that it reads..."* - Does `UNIX_TIMESTAMP` do that? – MadProgrammer Jan 27 '17 at 02:51
  • This is a copy of my answer, and the format mask is wrong. There is a space after seconds and `AM/PM`. – Tim Biegeleisen Jan 27 '17 at 02:55
  • There is not copy of your answer as mentioned in your comment it have difference in mask. That is first. And second: my answer show another function as its name as its params. – oklas Jan 27 '17 at 02:57
  • I am not sure what is 1493612100000 this is extracted automatically using this command Fieldcolumn.getTime() or exactly like so $F{start_date}.getTime() – Electronic Circuit Jan 27 '17 at 03:15