F****** timestamps/dates

Ignition
1

How can I convert the Date datatype in to seconds since epoch?

2

unix_timestamp() in MySQL. It varies from platform to platform. What DB are you using?

3

If you have a Date in scripting, this will convert it to seconds since epoch (Java epoch is Jan 1, 1970)

date = ... secondsSinceEpoch = data.getTime() / 1000

4

Thanks Carl,

I have a date from a date picker component. I needed to get it into epoch seconds to use in a database query to compare against a timestamp. (t_stamp column in sqlt_data_xxx table to be precise)

Nathan, I’m using PostGres.

The solution I worked out was to use:

select extract(epoch from timestamp with time zone '"+system.db.dateFormat(date, 'yyyy-MM-dd HH:mm:ss.SSSZ')+"')

I like Carl’s solution better. Why the ‘/1000’? does getTime() return milliseconds since epoch?

5

yep - milliseconds. See the javadocs for details.

6

I am trying to make sure my notes are right.

Datetime expression info:

Today 7am - addHours(midnight(now(0)), 7)
Yesterday 7am - addDays(addHours(midnight(now(0)), 7), -1)
2022-04-27 07:00:00

Datetime excel

=TEXT(TIMEVALUE(T1)-TIMEVALUE(S1),"hh:mm:ss")

Datetime Script info:

# Getting epoch time using parse
test = "2023-02-15 14:30:00"
epochTime = system.date.toMillis(system.date.parse(test, "yyyy-MM-dd HH:mm:ss"))
print(epochTime)

# Getting epoch time using setTime
now = system.date.now()
yesterday = system.date.addDays(now, -1)
passParamWhen1stShift = system.date.setTime(yesterday, 14,59,0)
print (passParamWhen1stShift)
epochTime = system.date.toMillis(passParamWhen1stShift)
print(epochTime)

Datetime SQL info:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
gets the start of the month, can be used to truncate to hour
DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()), 0)

select DATEDIFF(MONTH, 0, GETDATE()) will give the number of months from 1900-01-01
When 2012-07-01 it is 1350
select DATEADD(MONTH,1350,0) will give 2012-07-01 00:00:00.000
which is the start of that month.

Cast(getdate() as Date) will eliminate the time portion 2022-04-27 00:00:00 in SQL

7

Maybe run some tests against your code? Instead of playing necromancer with a 12 year old post?

6 Likes
8

It is the post that came up when I searched.
I want to find the information when I search.

Functionality isn't the concern, but technique.
I want to use good techniques.