How to convert Unix Epoch to Date

SQL++
1

I am a bit confused based on the documentation on how to take a Unix / Epoch Time and convert it into an ISO date

i tried for Example
SELECT DATE_FORMAT_STR('1463284740000','1111-11-11')
but that always produces null. So what i am looking for is a simple way to convert a Epoch/UNIX Timestamps to and from Date Format like 2017-01-31

2

MILLIS_TO_STR(1463284740000, ‘1111-11-11’)

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#fn-date-millis-to-str

1 Like
3

Ok so here is what i use but it doesn’t work the value of Start = 1596403203 which returns a “A”: “1970-01-19”,

SELECT d.*,
           MILLIS_TO_STR(`Start`, "1111-11-11") AS A
    FROM rets AS d
    WHERE d._type = "batch_info"
    ORDER BY d.`Start` DESC
    LIMIT 5

that results in this, not sure because when i use an external Epoch converter i get Sunday, August 2, 2020 9:20:03 PM

  {
    "A": "1970-01-19",
    "Class": "Residential",
    "End": 1596403206,
    "Limit": 1000,
    "New": 4,
    "NoChange": 46,
    "Query": "(ModificationTimestamp=2020-08-02T14:10:06+)",
    "Resource": "Property",
    "RunTime": 3,
    "Select": "",
    "Session": "13569bf746ba9899e6a723e44cd7bc6a173b10bb716",
    "Size": 83,
    "Start": 1596403203,
    "Update": 33,
    "_id": "1e3ed7fc-b7a0-4074-8806-b14448e24185",
    "_type": "batch_info"
  },
4

It looks like your start is seconds. MILLIS_TO_STR() first argument expects milliseconds. You need to multiply 1000