[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Help with GREATEST

Hi..
I'm using the greatest function on a series of dates.
It return the greatest in a wierd non data format

greatest(pci.updateddate,a.updateddate,dt.lastupdated) as LASTUPDATED

These fields are all datetime.

I get  values like
'-20130321134745'
'-20130321123715'

How can I format them as datetime?
0
JElster
Asked:
JElster
  • 4
  • 3
2 Solutions
 
Daniel WilsonCommented:
What's the underlying data type for these fields?  Your result looks something like the epoch or unix time.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

Does this solve it for you?
SELECT FROM_UNIXTIME( greatest(pci.updateddate,a.updateddate,dt.lastupdated)) as LASTUPDATED
0
 
JElsterAuthor Commented:
says DATEIME?

get a null back
0
 
Daniel WilsonCommented:
OK, let's back  up a step.  Are the values that are going into GREATEST non-null?

If you select all 3 of them for inspection ...

SELECT pci.updateddate,a.updateddate,dt.lastupdated
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
JElsterAuthor Commented:
I get a null using your function.

the result of

SELECT pci.updateddate,a.updateddate,dt.lastupdated

'2013-02-08 15:41:09'  , '2013-03-08 12:43:01',  '2014-03-18 19:11:11'
0
 
Daniel WilsonCommented:
So NULL's in the underlying data are not the issue.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest

Greatest is returning something other than datetime ... and not a valid epoch/unix time.

Let's try turning each argument into a UNIX timestamp before it is passed to GREATEST.

SELECT FROM_UNIXTIME( greatest(
   UNIX_TIMESTAMP(pci.updateddate),
   UNIX_TIMESTAMP(a.updateddate),
   UNIX_TIMESTAMP(dt.lastupdated)
)) as LASTUPDATED

Open in new window

0
 
JElsterAuthor Commented:
I get

'-1363888065'

????????
0
 
Daniel WilsonCommented:
Wow, this is not supposed to be working like that.

What version of MySql are you using?
0
 
GaryCommented:
MySQL stores date/time as UNIX time ergo you get 20130321134745

DATE_FORMAT(greatest(pci.updateddate,a.updateddate,dt.lastupdated),'%b %d %Y %h:%i %p') as LASTUPDATED

Reference for date parts formatting
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now