core.mysql.timestamp reverse function?

Third Party Coronium
#1

So my question is:

core.mysql.timestamp() will convert UNIX timestamp to mysql format

But how can I convert it back?

I found nothing about it in documentation. For now I’m using raw query:

SELECT UNIX\_TIMESTAMP(UpdatedDate) as UpdatedDate FROM ...

Thanks

#2

Hi,

If you don’t plan on using the functions that use timestamps in your queries, then just store the unix time using the  os.time  Lua method.

The MySQL module doesn’t have a method to reverse it, because it’s not common usage.

-dev

#3

Thanks for answer!

I would like to use some of those functions. And also it’s useful for me to see date & time in HeidiSQL

#4

Hi,

This function should work to covert to Unix timestamp from MySQL. I’ll add it in the next release. But you can add it locally to your server-side API for now.

Code

local function tsToUnix(mysql\_ts) local pattern = "(%d+)%-(%d+)%-(%d+) (%d+):(%d+):(%d+)" local xyear, xmonth, xday, xhour, xmin, xsec = mysql\_ts:match(pattern) local unixTs = os.time({year=xyear, month=xmonth, day=xday, hour=xhour, min=xmin, sec=xsec}) return unixTs end

Example

local unix\_ts = tsToUnix( "2018-04-23 20:39:39" ) print( unix\_ts ) --\> 1524533979

Hope that helps.

-dev

#5

Many thanks for this function!

#6

Hi,

If you don’t plan on using the functions that use timestamps in your queries, then just store the unix time using the  os.time  Lua method.

The MySQL module doesn’t have a method to reverse it, because it’s not common usage.

-dev

#7

Thanks for answer!

I would like to use some of those functions. And also it’s useful for me to see date & time in HeidiSQL

#8

Hi,

This function should work to covert to Unix timestamp from MySQL. I’ll add it in the next release. But you can add it locally to your server-side API for now.

Code

local function tsToUnix(mysql\_ts) local pattern = "(%d+)%-(%d+)%-(%d+) (%d+):(%d+):(%d+)" local xyear, xmonth, xday, xhour, xmin, xsec = mysql\_ts:match(pattern) local unixTs = os.time({year=xyear, month=xmonth, day=xday, hour=xhour, min=xmin, sec=xsec}) return unixTs end

Example

local unix\_ts = tsToUnix( "2018-04-23 20:39:39" ) print( unix\_ts ) --\> 1524533979

Hope that helps.

-dev

#9

Many thanks for this function!