Hi folks, I’m trying to fetch the lastModifiedDate for Inventory Items with a query in SuiteQL but I get the format 04/16/2023. This is a datetime field judging by the docs and I want the Epoch time or some kind of proper timestamp but can’t seem to get that. Does anyone know how to? Is it not stored in some kind of Unix time?

@battk as in a primitive TO_CHAR(timestamp)-TO_CHAR(timestamp) and then convert to nanoseconds to get epoch?

use the second parameter to choose the format of your datetime

@battk I’m not quite following. I need the date in epoch or Unix time, but the current format is “04/16/2023”. The second parameter of the TO_CHAR function as far as I know only accepts formats like “YYYY-MM-DD HHMMss” and so on, but I want to get a timestamp that is nanoseconds from 1970-01-01 000000

you can get it to a iso-8601 related format using TO_CHJAR

@battk yes but where do I go from there, how do I get it to epoch?

preferably leave it as the string

@battk so you mean I shouldn’t try to convert it to epoch or unix but just use i.e. the iso-8601 string as my timestamp?

ideally you use the timestamp

@battk ok cool thanks! I wish there was an easier way to extract it as comparing epoch timestamps in deltas etc is a pretty conventional way of doing things in integrations but hey 🙂

You can do something like:

SELECT to_char(sys_extract_utc(lastmodifieddate), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS lastmodifieddate_iso8601
FROM item
FETCH FIRST 1 ROW ONLY