LogoLogo
ProductsPricingGlossary Launch Acho
  • 🎉Welcome
  • Introduction
  • What is Acho?
  • How does Acho work?
    • Proof of Concept (PoC)
  • App Builder
    • Get Started
    • Core Concepts
    • Overview
    • App construction
      • App Configuration
      • Pages
      • Interactions
        • Add an interaction
        • Add conditions to interactions
        • Event payload
        • Ordering interactions with action flow
        • Transformer
        • Actions
          • Navigation
          • Element
          • Data Source
          • App
          • Page
          • API Service
          • Database
          • Media Service
      • Table
      • Metric
      • Query
      • Data Store
      • Elements
        • Table & Chart
          • Searchable Table
          • Table
          • Chart
          • Score Card
        • Form Elements
          • Form
          • Search Bar
          • Checkbox
          • Date Picker
          • Custom Form
          • Input
          • Multiselect
          • Radio Button
          • Radio List
          • Rich Text Editor
          • Select
          • Switch
          • Textarea
          • Upload
        • Web Elements
          • Badge
          • Button
          • Clickable
          • Collapse Menu
          • Divider
          • Icon
          • Image
          • Link
          • Message
          • Modal
          • Notification
          • Pagination
          • Popover
          • Rate
          • Rich Text
          • Tabs
          • Text
        • Layout Elements
          • Container
          • List
        • Advanced elements
          • Code Block
          • Condition
          • Print
        • CSS Styles
          • General techniques
          • Layout
          • Spacing
          • Size
          • Position
          • Typography
          • Background
          • Border
          • Effect
        • Form Check
        • Tooltip
      • Accessors
      • Plugin Store
    • Popular Use Cases
      • Build a chart from Table Nodes
      • Create a table
      • Create a list
      • Create a filter
      • Create a search bar
      • Use Custom Form Container to collect user inputs
      • Drill down on a table
      • Download file from query node
      • Set loading animations
      • Modify a database
      • Navigate to a different page
      • Set up dynamic routing
      • Update app data using accessors
      • Create a Chart with Code Block and D3 Library
      • Rich text and rich text editor
      • Send an Email via Mailjet Plugin
    • Preview
    • Publish
      • Embed published app on website
    • Version Control
    • App User Management
      • Onboarding Page
      • Sign in page
      • Guest Isolation and Private Session
    • Theme
    • FAQ
  • Acho Studio
    • Quick Start
    • Resources
      • Add a resource
      • Supported data sources
        • Airtable
        • Amazon Ads
        • Amplitude
        • Amazon Seller Partner
        • Apify Dataset
        • Appstore
        • Asana
        • API Connector
        • API Template
        • AWS CloudTrail
        • Azure Table Storage
        • Batch Files
        • Bamboo HR
        • BigCommerce
        • BigQuery
        • Bing Ads
        • Braintree
        • Cart.com
        • Chargebee
        • Chargify
        • Chartmogul
        • Clickhouse
        • Close.com
        • CockroachDB
        • Confluence
        • Customer.io
        • Delighted
        • Dixa
        • Drift
        • Facebook Marketing
        • Freshdesk
        • Freshsales
        • Freshservice
        • GitHub
        • GitLab
        • Google Ads
        • Google Analytics
        • Google Sheets
        • Google Search Console
        • Greenhouse
        • Harness
        • Harvest
        • HubSpot
        • IBM Db2
        • Instagram
        • Intercom
        • Iterable
        • Jenkins
        • Jira
        • Kafka
        • Klaviyo
        • Kustomer
        • Lemlist
        • Lever Hiring
        • LinkedIn Ads
        • Linnworks
        • Mailchimp
        • Marketo
        • Microsoft Teams
        • Mixpanel
        • Monday
        • MongoDB
        • MySQL
        • My Hours
        • NetSuite
        • Notion
        • Okta
        • OneDrive
        • OneSignal
        • Oracle DB
        • Outreach
        • PagerDuty
        • Paypal Transaction
        • Paystack
        • Pipedrive
        • PersistIq
        • Pinterest
        • Plaid
        • PostgreSQL
        • PostHog
        • PrestaShop
        • Qualaroo
        • QuickBooks
        • Recharge
        • Recurly
        • Remote File
        • S3
        • Salesforce
        • Salesloft
        • SearchMetrics
        • Sendgrid
        • Sentry
        • Short.io
        • Shopify
        • Single File
        • Slack
        • Smartsheets
        • Snapchat Marketing
        • Snowflake
        • SQL Server
        • Square
        • Stripe
        • SurveyMonkey
        • Tempo
        • TikTok Marketing
        • Trello
        • Twilio
        • Typeform
        • VictorOps
        • WooCommerce
        • YouTube Analytics
        • Zendesk Chat
        • Zendesk Sunshine
        • Zendesk Talk
        • Zendesk Support
        • Zenloop
        • Zoho CRM
        • Zoom
        • Zuora
    • Data Prep (Projects)
      • Create a Data Prep Project
        • Create a table tab
        • Copy a table tab
        • Hide a table tab
      • Apply an action
        • View
          • Hide Columns
          • Filter
          • Sort
          • Cut
          • Rename
          • Move Column
        • Combine Tables
          • Join
          • Union
        • Transformation
          • Pivot Table
          • Unpivot
        • Data Cleaning
          • Cleanse
          • Replace
          • Change Type
          • Deduplicate
          • Split
          • Flatten
        • Tools
          • Formula
          • SQL Editor
            • SQL Editor Queries
            • All functions in Formula/SQL
              • Mathematical Functions
              • String Functions
              • Date Functions
              • Datetime Functions
              • Time Functions
              • Timestamp Functions
              • Conditional Functions
              • Type Conversion
              • Other Functions
        • Reserved Keywords
      • Pipeline
      • Edit your pipelines
      • Generate column summary for inspection
      • Use URL to access and share your project
      • Write a description for your project
      • Delete a resources/project/tab
    • Data Flow
      • Export data
        • Download CSV files
        • Excel Connector
        • Google Sheets Connector
        • Embed a table on your website
          • Data Usage Agreement
        • Apache Superset
        • Chartio
        • Looker
        • Metabase
        • Power BI
        • Qlik
        • Tableau
        • MongoDB
        • MySQL
        • MSSQL
        • Snowflake
        • PostgreSQL
        • BigQuery
    • Set up Data Sync
    • FAQ
  • Organization
    • Get started
    • Create an organization
    • Invite people to your organization
    • Share your resources
    • Share your projects
    • Share your apps
    • Change payment method on file
  • FAQ
    • I can't find data after uploading a big CSV file
Powered by GitBook
On this page
  • CURRENT_TIMESTAMP
  • TIMESTAMP
  • EXTRACT
  • TIMESTAMP_ADD
  • TIMESTAMP_SUB
  • TIMESTAMP_DIFF
  • TIMESTAMP_TRUNC
  • FORMAT_TIMESTAMP
  • PARSE_TIMESTAMP
  • TIMESTAMP_SECONDS
  • TIMESTAMP_MILLIS
  • TIMESTAMP_MICROS
  • UNIX_SECONDS
  • UNIX_MILLIS
  • UNIX_MICROS
  • Details about date_part
  • Supported Format Elements

Was this helpful?

  1. Acho Studio
  2. Data Prep (Projects)
  3. Apply an action
  4. Tools
  5. SQL Editor
  6. All functions in Formula/SQL

Timestamp Functions

PreviousTime FunctionsNextConditional Functions

Last updated 3 years ago

Was this helpful?

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

Description

Return the current timestamp.

Result Data Type

TIMESTAMP

Example

CURRENT_TIMESTAMP()

Result: 2021-04-26 18:23:58.422989 UTC

TIMESTAMP

TIMESTAMP(timestamp_expression, [timezone])

Description

Convert a string, date, or datetime expression to a timestamp object.

Input

  • timestamp_expression: a STRING, DATE, DATETIME, TIMESTAMP object or column

  • timezone: (optional, STRING) The default timezone is UTC. You can optionally specify a

Result Data Type

TIMESTAMP

Example

TIMESTAMP("2020-12-25 15:30:00 UTC")

Result: 2020-12-25 15:30:00 UTC

EXTRACT

EXTRACT(date_part FROM timestamp [AT TIME ZONE timezone])

Description

Return a part of the given timestamp.

Input

  • timestamp: a TIMESTAMP object or column

Return Data Type

INTEGER, except when:

  • part is DATE, returns a DATE object.

  • part is DATETIME, returns a DATETIME object.

  • part is TIME, returns a TIME object.

Example

EXTRACT(DAY FROM TIMESTAMP(""2008-12-25 05:30:00+00""))

Result: 25

EXTRACT(DATE FROM TIMESTAMP("2008-12-25 05:30:00+00"))

Result: 2008-12-25

EXTRACT(DAY FROM TIMESTAMP("2008-12-25 05:30:00+00") AT TIME ZONE "America/Los_Angeles")

Result: 24

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp, INTERVAL integer date_part)

Description

Add a specified time interval to the given timestamp.

Input

  • timestamp: a TIMESTAMP object or column

  • integer: the number of date_part

Result Data Type

TIMESTAMP

Example

TIMESTAMP_ADD(TIMESTAMP("2020-12-25 15:30:00+00"), INTERVAL 10 MINUTE)

Result: 2020-12-25 15:40:00 UTC

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp, INTERVAL integer date_part)

Description

Subtract a specified time interval to the given timestamp.

Input

  • timestamp: a TIMESTAMP object or column

  • integer: the number of date_part

Result Data Type

TIMESTAMP

Example

TIMESTAMP_SUB(TIMESTAMP("2020-12-25 15:30:00+00"), INTERVAL 10 MINUTE)

Result: 2020-12-25 15:20:00 UTC

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp1, timestamp2, date_part)

Description

Return the difference between two timestamps (timestamp1-timestamp2). The difference is expressed in units of date_part.

Input

  • timestamp1: a TIMESTAMP object or column

  • timestamp2: a TIMESTAMP object or column

Result Data Type

TIMESTAMP

Example

TIMESTAMP_DIFF(TIMESTAMP("2020-12-25 13:00:00+00"), TIMESTAMP("2020-12-25 15:30:00+00"), HOUR)

Result: -2

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp, date_part, [timezone])

Description

Truncate a specific timestamp to the start of a given unit of duration and returns a new timestamp.

Input

  • timestamp: a TIMESTAMP object or column

Result Data Type

TIMESTAMP

Example

TIMESTAMP_TRUNC(TIMESTAMP("2020-12-25 15:30:00+00"), DAY, "UTC")

Result: 2020-12-25 00:00:00 UTC

TIMESTAMP_TRUNC(TIMESTAMP("2020-12-25 15:30:00+00"), DAY, "America/Los_Angeles")

Result: 2020-12-25 08:00:00 UTC

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format, timestamp, [timezone])

Description

Transforms a timestamp variable into a string variable in a specific format.

Input

  • timestamp: a TIMESTAMP object or column

Result Data Type

STRING

Example

FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2020-12-25 15:30:00+00", "UTC")

Result: Dec-25-2020

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format, timestamp_expression, [timezone])

Description

Convert a string to a timestamp variable according to the specified format.

Input

  • timestamp_expression: a STRING object or column

Result Data Type

TIMESTAMP

Example

PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2020")

Result: 2020-12-25 07:30:00 UTC

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(integer)

Description

Convert a UNIX timestamp in seconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of seconds since 1970-01-01 00:00:00 UTC.

Result Data Type

TIMESTAMP

Example

TIMESTAMP_SECONDS(1600219000)

Result: 2020-09-16 01:16:40 UTC

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(integer)

Description

Convert a UNIX timestamp in milliseconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of milliseconds since 1970-01-01 00:00:00 UTC.

Result Data Type

TIMESTAMP

Example

TIMESTAMP_MILLIS(1600219123456)

Result: 2020-09-16 01:18:43.456 UTC

TIMESTAMP_MICROS

TIMESTAMP_MICROS(integer)

Description

Convert a UNIX timestamp in microseconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of microseconds since 1970-01-01 00:00:00 UTC.

Result Data Type

TIMESTAMP

Example

TIMESTAMP_MICROS(1600219123456789)

Result: 2020-09-16 01:18:43.456789 UTC

UNIX_SECONDS

UNIX_SECONDS(timestamp)

Description

Return the number of seconds since 1970-01-01 00:00:00 UTC.

Result Data Type

INTEGER

Example

UNIX_SECONDS(TIMESTAMP("2008-12-25 15:30:00+00"))

Result: 1608910200

UNIX_MILLIS

UNIX_MILLIS(timestamp)

Description

Return the number of milliseconds since 1970-01-01 00:00:00 UTC.

Result Data Type

INTEGER

Example

UNIX_MILLIS(TIMESTAMP("2020-12-25 15:30:00+00"))

Result: 1608910200000

UNIX_MICROS

UNIX_MICROS(timestamp)

Description

Return the number of microseconds since 1970-01-01 00:00:00 UTC.

Result Data Type

INTEGER

Example

UNIX_MICROS(TIMESTAMP("2008-12-25 15:30:00+00"))

Result: 1608910200000000

Details about date_part

Part

Description

MICROSECOND

The microsecond of the time.

MILLISECOND

The millisecond of the time.

SECOND

The second of the time.

MINUTE

The minute of the time.

HOUR

The hour of the time.

DAYOFWEEK

The weekday from 1 to 7 with Sunday as the first day of the week.

DAY

The day of the month.

DAYOFYEAR

The day of the year.

WEEK

The week number of the date from 0 to 53. (Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0)

WEEK(<WEEKDAY>):

The week number of the date from 0 to 53. (You can optionally specify the first weekday of a week and weeks begin with the weekday that you specified. Dates prior to the first <WEEKDAY> of the year are in week 0. Valid values for <WEEKDAY> are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.)

ISOWEEK

Returns the ISO 8601 week number of the date from 1 to 53 with Monday as the first day of the week. (The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.)

MONTH

Return the month of the date.

QUARTER

Return values in the range between 1 and 4.

YEAR

Return the month of the year.

ISOYEAR

Return the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date_expression belongs.

DATE

Return the DATE object in a format of "yyyy-mm-dd".

DATETIME

Return the DATETIME object in a format of "yyyy-mm-dd HH:MM:SS".

TIME

Return the TIME object in a format of "HH:MM:SS".

Supported Format Elements

Format element

Description

Example

%A

Return the full weekday name.

Tuesday

%a

Return the abbreviated weekday name.

Tue

%B

Return the full name of the month.

May

%b or %h

Return the abbreviated name of the month.

May

%C

Return the century

20

%c

Return the date and time in a format of "%a %b %e %T %Y"

Tue May 4 15:31:13 2021

%D

Return the date in a format of "%m/%d/%y".

05/04/21

%d

Return the day of the month.

04

%e

Return the day of month without leading 0.

4

%F

Return the date in a format of "%Y-%m-%d"

2021-05-04

%G

2021

%g

Return the ISO 8601 year without the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.

21

%H

Return the hour in the 24-hour clock (00-23).

15

%I

Return the hour in the 12-hour clock (01-12).

03

%j

Return the day of the year (001-366).

124

%k

Return the hour (0-23) in the 24-hour clock without leading 0.

15

%l

Return the hour (1-12) in the 12-hour clock without leading 0

3

%M

Return the minute of the time (00-59).

31

%m

Return the month of the date (01-12).

05

%n

Return a newline character.

%P

Return a lowercase "am" or "pm".

pm

%p

Return an uppercase "AM" or "PM".

PM

%Q

Return the quarter of the date (1-4).

2

%R

Return the time in a format of "%H:%M".

15:31

%r

Return the time in the 12-hour clock with AM/PM notation.

03:31:13 PM

%S

Return the second of the time (00-60).

13

%T

Return the time in a format of %H:%M:%S.

15:31:13

%t

Return a tab character.

%U

Return the week number (00-53) of the year. Sunday is the first day of the week

18

%u

Return the weekday of the date as 1-7. Monday is the first day of the week.

2

%V

Return the ISO 8601 week number (01-53) of the year. Monday is the first day of the week. If the week containing January 1 has four or more days in the new year, then it returns "week 1". Otherwise, it returns "week 53" of the previous year, and considers the next week as "week 1".

18

%W

Return the week number(00-53) of the year. Monday is the first day of the week.

18

%w

Return the weekday of the date as 0-6. Sunday is the first day of the week.

2

%X

Return the time in a format of "HH:MM:SS"

15:31:13

%x

Return the date in a format of "MM/DD/YY"

05/04/21

%Y

Return the year with the century

2021

%y

Return the year (00-99) without the century with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are the 2000s, while years 69-99 are the 1900s.

21

%%

Return a single "%" character.

%

%E#S

Return the seconds with the given number of digits of fractional precision. The "#" should be replaced by a number.

"%E3S" returns 37.021

%E*S

Return the seconds with full fractional precision.

37.021851

%E4Y

Return the year in 4 digits (0000-9999).

2021

date_part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, DATETIME, or TIME (See more )

AT TIME ZONE timezone: You can optionally specify a to convert the timestamp into different timezones before extracting the part of the timestamp.

date_part: The unit of the time interval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, or DAY (See more )

date_part: The unit of the time interval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, or DAY (See more )

date_part: The unit of the difference between two timestamps. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK(begins on Sunday), ISOWEEK(begins on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more )

date_part: The unit of duration. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, WEEK(), ISOWEEK, MONTH, QUARTER, YEAR or ISOYEAR (See more )

timezone: You can optionally specify a

format: an expected format of a timestamp. It is expressed in a STRING. To see supported format elements, visit .

timezone: You can optionally specify a

format: a STRINGobject. To see supported format elements, visit .

timezone: You can optionally specify a

Return the year with the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year.

time zone.
time zone
time zone.
time zone.
time zone.
details
details
details
details
details
here
here
ISO 8601